Support Board
Date/Time: Fri, 22 Nov 2024 07:49:52 +0000
[User Discussion] - Average of non-zero values in a spreadsheet
View Count: 2168
| [2013-05-09 09:46:51] |
| Bedhog - Posts: 179 |
|
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: