• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Averages to exclude Zeros

Joe Shaer

New Member
Hello All,

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


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

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.

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