Support Board
Support Board
Search Board
Control Panel
View My Posts / Threads
Direct MessagesDate/Time: Fri, 04 Oct 2024 15:42:47 +0000
AVERAGE exclude blanks
View Count: 1157
| [2014-11-06 23:54:50] |
| jivetrader - Posts: 410 |
|
using Sierra spreadsheets, how to get an AVERAGE to ignore blank cells?
|
| [2014-11-07 02:46:31] |
| |
|
It already does. We tested this.
Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing |
| [2014-11-07 03:50:42] |
| jivetrader - Posts: 410 |
|
yes, i read that in the documentation. however this is not working for me. i am getting a Value! error. the column i am trying to average has positive and negative values and many cells that contain "" (ie nothing) from a previous IF formula.
|
| [2014-11-07 04:30:27] |
| |
|
The VALUE error is occurring because you have empty strings (""). Make sure those cells contain a 0 rather than an empty string. This will solve the problem.
Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing |
| [2014-11-07 05:42:41] |
| jivetrader - Posts: 410 |
|
tried that and then my average was off due to the zeros what do u recommend |
| [2014-11-07 06:04:22] |
| Sawtooth - Posts: 4093 |
|
Here is an example of a way to do it: =SUM(AC3:AC1002)/COUNTIF(AC3:AC1002,">0") |
| [2014-11-07 07:53:48] |
| |
|
We now understand the nature of the problem. We will add a new function: AVERAGE_IGNOREZEROS Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing |
| [2014-11-07 07:56:58] |
| jivetrader - Posts: 410 |
|
thank you both
|
| [2014-11-07 19:35:58] |
| jivetrader - Posts: 410 |
|
could you also add the function: MEDIAN_IGNOREZEROS |
| [2014-11-07 20:07:21] |
| jivetrader - Posts: 410 |
|
this behaviour of not properly ignoring "" cells also occurs with STDEV
|
| [2015-01-01 19:10:55] |
| |
|
We will be looking into the last two postings.
Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing |
| [2015-03-10 17:33:37] |
| Jeffrey - Posts: 2152 |
|
The cleanest solution to this problem is to use the NOVALUE named constant in the IF formula functions, rather than "". NOVALUE, unlike "", is equivalent to an empty cell. Example: =IF(E3 > 100, E3, NOVALUE).
|
| [2015-03-10 18:16:37] |
| Sawtooth - Posts: 4093 |
|
Nice, but it only works in the New Spreadsheets, and it is not documented. |
| [2015-03-10 18:31:56] |
| jivetrader - Posts: 410 |
|
thanks jeffrey, i did not know about NOVALUE.
|
To post a message in this thread, you need to log in with your Sierra Chart account: