Forum Discussion

Nickerz_2020

Brass Contributor

Apr 23, 2021

Solved

Is There An Easier Way?

I have cells that show a % (i.e., 40%, 45%, etc.) for each day of the week (Monday-Sunday). I'm trying to get the average for the entire week but some days are 0%. I tried using the following formula...

=(I17+J17+K17+L17+M17+N17+O17)/7

but this formula calculates the 0% for some days, bringing down the average for the week which I don't want. If a cell is 0%, can I have the average not include this so it doesn't skew my average?

Thanks in advance!

Formulas and Functions

JMB17
Apr 23, 2021
Do you have the AverageIf function?

=AVERAGEIF(I17:O17,">0")

19 Replies

JMB17
Bronze Contributor
Apr 23, 2021
Do you have the AverageIf function?

=AVERAGEIF(I17:O17,">0")
- Nickerz_2020
  Brass Contributor
  Apr 23, 2021
  YES, THAT WAS IT!!!!!! It works. I'm embarrassed to say what my old formula was to accomplish this and thought...there's got to be an easier way - lol!
  
  Thank you JMB17!
  - Nickerz_2020
    Brass Contributor
    Apr 23, 2021
    One more thing...
    
    If the row is all 0% how do I get that same ending cell (P17) to show a 0.00% instead of #DIV/0! I was thinking the =IFERROR and then ,0 at the end but I tried this and it didn't work.
Riny_van_Eekelen
Platinum Contributor
Apr 23, 2021
Nickerz_2020 Use AVERAGE like this:
=AVERAGE(I7:O7)
- Nickerz_2020
  Brass Contributor
  Apr 23, 2021
  Hi Riny,
  
  I tried your formula but then I get 13000% so then I typed in the following formula (thinking it should be 17 not 7) but I get the same result as my original formula...
  
  =AVERAGE(I17:O17)