An "Average" formula which does not consider a cell with "zero" value

Ahmet - newby

Hi Smartsheet Community,

I'm looking for an "average" formula for a set of cells. However, some of the cells have a "zero" value and the formula musn't average across those cells, only ones which have a positive value.

Please see an example below:

Average of only cells which have a number - if the number is "0", then do not include in average calculation

Jan Feb Mar Average

Category A 0 0 15 ?

Category B 0 5 10 ?

Category C 10 15 0 ?

Thanks for your support...

Ahmet

Nick Korna

Hi @Ahmet - newby ,

You can do this using the AVERAGEIF formula. In your example:

=AVERAGEIF(Jan@row:Mar@row, >0, Jan@row:Mar@row)

Output:

Hope this helps!

Ahmet - newby

Thank you so much Nick. I think I'm almost there but one slight tweak needed in the formula...

Could you also show me the formula if I was to select the cells separately; so if I want to calculate the average value of Category A Jan, Category B Feb and Category C Feb (0+5+15) - without averaging the "0" Cat A Jan?

Hope that makes sense? Cheers...

Nick Korna

I'm not sure there is an easy way to do it while having the 0s in there if you're taking values from different columns/rows rather than in a line either horizontally/vertically.

However, you can get round this by leaving 0 value cells blank and just using AVG. In your example, if Category A in Jan is blank instead of 0 as blank cells are ignored:

=AVG(Jan1, Feb2, Feb3)

=(5+15/2)=10

This would also have the side effect of mean you can also use AVG in the other calculation instead of needing AVERAGEIF.

Ahmet - newby

hmmm I may have to re-order my cells but that will be a little difficult.

I'll keep searching but also look at your formulas and try different variations.

Thanks very much for your support and suggestions...