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
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!