# Support Board

Date/Time: Tue, 27 Feb 2024 00:12:01 +0000

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

### View Count: 1946

 [2013-05-09 09:46:51] #1 Bedhog - Posts: 165 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] #2 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] #3 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] #4 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))