Login Page - Create Account

Support Board


Date/Time: Sat, 21 Dec 2024 18:09:28 +0000



AVERAGE exclude blanks

View Count: 1198

[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]
Sierra Chart Engineering - Posts: 104368
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]
Sierra Chart Engineering - Posts: 104368
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: 4141
Here is an example of a way to do it:
=SUM(AC3:AC1002)/COUNTIF(AC3:AC1002,">0")
[2014-11-07 07:53:48]
Sierra Chart Engineering - Posts: 104368
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]
Sierra Chart Engineering - Posts: 104368
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: 2187
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: 4141
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:

Login

Login Page - Create Account