Averages to exclude Zeros

[Joe Shaer]

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?

Thanks in advance

 

[vletm]

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?

 

[Peter Bartholomew]

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.

= 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.