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

Options
✭✭✭✭

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.

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 ?

Ahmet

Tags:

• ✭✭✭✭✭✭
Options

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!

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!