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 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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!