Login Page - Create Account

Support Board


Date/Time: Sat, 26 Nov 2022 22:27:33 +0000



[User Discussion] - Average of non-zero values in a spreadsheet

View Count: 1605

[2013-05-09 09:46:51]
Bedhog - Posts: 163
Hello All,

I have an SC spreadsheet with the following 10 rows.

What formula could I use to create an average of the non-zero values?

9634
9636
9638
9640
0
0
0
0
0
0

=AVERAGE(A1:A10) is returning 3854.8 when the number I really want is =AVERAGEofNonZeroValues(A1:A10) returning 9637

Thank you.



[2013-05-09 12:23:49]
TechTrader - Posts: 116
One method NOT using the AVERAGE function.
SC spreadsheet formulas are entered in row 3.
Assuming your numbers 9634-9640-0 are in column K row 3 to row 12 (10 rows)
Type in column L, row 3 =IF(K3<>0,1,0)
Type in column M, row 3 =SUM(L3:L12)
Type in column N, row 3 =SUM(K3:K12)/M3
Column N row 3 is the average of non zero numbers in column K, row 3 to row 12 (10 rows)

Note above is untested.







Date Time Of Last Edit: 2013-05-09 12:53:42
[2013-05-10 12:38:46]
vegasfoster - Posts: 444
If you want to put it in a single formula, you can use

=SUM(A1:A10)/SUMPRODUCT(--(A1:A10<>0))
[2013-05-10 12:48:48]
vegasfoster - Posts: 444
Also if you wanted to use multiple conditions, e.g. not zero and less than 500, then you could use something like

=SUMPRODUCT(--(A1:A10<>0), --(A1:A10<500), A1:A10)/SUMPRODUCT(--(A1:A10<>0), --(A1:A10<500))

To post a message in this thread, you need to log in with your Sierra Chart account:

Login

Login Page - Create Account