An "Average" formula which does not consider a cell with "zero" value
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
Answers

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!

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

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.

hmmm I may have to reorder 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
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 386 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 125 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!