# Averages to exclude Zeros

#### Joe Shaer

##### New Member
Hello All,

I have the following formula which averages values from different columns:

=AVERAGEIFS(\$DL\$9:\$DL\$2000,\$G\$9:\$G\$2000,GS12)+AVERAGEIFS(\$DN\$9:\$DN\$2000,\$G\$9:\$G\$2000,GS12)+AVERAGEIFS(\$DR\$9:\$DR\$2000,\$G\$9:\$G\$2000,GS12)+AVERAGEIFS(\$DX\$9:\$DX\$2000,\$G\$9:\$G\$2000,GS12)+AVERAGEIFS(\$DZ\$9:\$DZ\$2000,\$G\$9:\$G\$2000,GS12)+AVERAGEIFS(\$DV\$9:\$DV\$2000,\$G\$9:\$G\$2000,GS12)+AVERAGEIFS(\$DT\$9:\$DT\$2000,\$G\$9:\$G\$2000,GS12)+AVERAGEIFS(\$DP\$9:\$DP\$2000,\$G\$9:\$G\$2000,GS12)

I noticed the 'AVERAGEIFS' function is including Zeros as part of the result - which is skewing my overall average.

Is there a way to EXCLUDE the zero from the average WITHOUT needing to insert "<>0" inside each sub-formula as I have over 30 of these formulas throughout my spreadsheet?

#### Joe Shaer​

Four questions:
Do You mean that if average_range has zeros then those skewing Your overall average?
Are You 100% that any of those zeros are real values?
Could You left those 'no need zeros' as blanks?
... if You've over 30 those kind of formulas --- could those be something else?

Are you sure you should sum the averages of a number of ranges? A more conventional calculation would be to sum the contributions from each and then to divide by the total count, giving an overall average. The "<>0" is necessary if you wish to discount zeros. FALSE and blanks are ignored by default.

I guess you have already realised that the layout is a nightmare for any kind of efficient calculation. Something I have done in the attached is to perform the conditional sum over every column and then drop anything that arises from the alternate columns that you are not interested in.

Code:
``````= LET(
columnSums,   BYCOL(valueRanges, LAMBDA(valueColumn, SUMIFS(valueColumn, criterionRange, criterionValue, valueColumn, "<>0"))),
rangeSum,     SUM(DROP(WRAPROWS(TOCOL(columnSums), 2),,-1)),
columnCounts, BYCOL(valueRanges, LAMBDA(valueColumn, COUNTIFS(criterionRange, criterionValue, valueColumn, "<>0"))),
rangeCount,   SUM(DROP(WRAPROWS(TOCOL(columnCounts), 2),,-1)),
rangeSum / rangeCount
)``````

That formula is probably an improvement on picking out remote ranges with a 'knife and fork' but its still lacks charm.