SOLVED

Is There An Easier Way?

Brass Contributor

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!

 

19 Replies

@Nickerz_2020 Use AVERAGE like this:

=AVERAGE(I7:O7)

 

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)
best response confirmed by Nickerz_2020 (Brass Contributor)
Solution
Do you have the AverageIf function?

=AVERAGEIF(I17:O17,">0")
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!
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.

@Nickerz_2020 

It shall work

=IFERROR(AVERAGEIF(I17:O17,"<>0",I17:O17),0)
Yes, that worked perfectly! Thank you Sergei!

I really appreciate all the support in this forum. Thank you everyone.

@Nickerz_2020 , glad to know it helped

It absolutely did, thx!

I've got one more (I think). Sorry but I'm working on my report and have way too many formulas going on that bounce off each other. I tried using the last formula's principle but it didn't work as it's slightly different. So here's my question...

How do I type the formula to get the daily average IF the cells aren't next to each other (i.e., I16:O16)? I tried typing the following formula...

=IFERROR(AVERAGEIF(S33,AC33,AM33,AW33,BG33,BQ33,CA33,"<>0", S33,AC33,AM33,AW33,BG33,BQ33,CA33),0)


...but Excel says I've entered too many arguments for this cell.

Then I tried playing around with it and I did get this to work...

=S33,AC33,AM33,AW33,BG33,BQ33,CA33

...but it counts all cells with 0.00 and skews the daily average showing only .96PPH. The correct avg. is 1.12PPH…

So it's calculating some of the cells that are 0.00 and skewing my average which I don't want. If any of the above cells are 0.00 I want the formula to not count it in the average for the day. Hope that makes sense.

@Nickerz_2020 

Depends on your version of Excel. As variant

=LET(values, INDEX(S33:CA33,1,SEQUENCE(,7,1,10)),
     IFERROR(SUM(values)/SUM(--(values<>0)),0))
I tried copying and pasting your formula above and inserted my cells into the areas that said "values" but get errors. For example, I tried typing the following combinations...

IFERROR(SUM(S33,AC33,AM33,AW33,BG33,BQ33,CA33)/SUM((S33,AC33,AM33,AW33,BG33,BQ33,CA33<>0)),0))

=LET(S33,AC33,AM33,AW33,BG33,BQ33,CA33,INDEX(S33:CA33,1,SEQUENCE(,7,1,10)),IFERROR(SUM(S33,AC33,AM33,AW33,BG33,BQ33,CA33)/SUM(--(S33,AC33,AM33,AW33,BG33,BQ33,CA33<>0)),0))

last, I tried copying and pasting exactly what you typed...
=LET(values, INDEX(S33:CA33,1,SEQUENCE(,7,1,10)),
IFERROR(SUM(values)/SUM(--(values<>0)),0))


I know it's hard to explain since my spreadsheet can be unique in so many ways but I'd like to calculate the different PPH (Presentations Per Hour) by adding them up (i.e., 1.09, 0.40, 0.22, 0.68, 2.03, 2.33, 0.00) and then give me the average of them all combined but without the average being skewed by the 0.00 as this decreases the real average.

So when I click on each of the cells with a PPH average, the correct average should be 1.12PPH but because it is counting the 0.00, it's bringing down the average to .96PPH - which I don't want. The sum should be 1.12PPH. Sorry if I'm confusing this even more.
If it is certain there will be no negative numbers, you could also try:
=IFERROR(SUM(S33,AC33,AM33,AW33,BG33,BQ33,CA33)/INDEX(FREQUENCY((S33,AC33,AM33,AW33,BG33,BQ33,CA33),0),2),0)

But, I assume this is part of a table? Is there a specific table header for these values that could be used to identify these values? Say the headers are in Row 1 and the specific header for these values is "x", then maybe you could something similar to this instead of dealing with a non-contiguous range (and, assuming all the values in the range are numeric):

=SUM((S1:CA1="x")*S33:CA33)/SUM((S1:CA1="x")*(S33:CA33<>0))
JMB...you did it again! Yes, I copied and pasted your first formula and it worked! Thank you, thank you, thank you! I think I'm done with my report. It's a crazy spreadsheet that I've added values to over the years and it's become a beast. So again thank you to all for your help!

I'm sure I'll be back asking more questions so thanks also for your patience from a noob. =)

And I'm back but I'm sure this is the easiest one. I just need to add the =IFERROR feature so it shows a 0 in the cell if there is no data in it. I know =IFERROR(P33/L33,0) is used in one of my cells but this isn't the one I need so I tried using the logic but can't get it to work for the following formula...

=(P34+Z34+AJ34+AT34+BD34+BN34+BX34)/J14

 

The error in the cell btw is...

#DIV/0!

Sorry, I just can't understand where place the ",0)" depending on the type of formula. ??????

Thank you in advance!

Try:
=Iferror((P34+Z34+AJ34+AT34+BD34+BN34+BX34)/J14,0)
Yep...that worked too! Thank you again JMB! I tried this but saw in the one tried, I didn't close the parentheses at the very end. Very nice and have a good weekend everyone.

@Nickerz_2020 

Not sure why do you have an error, I attached sample file to check the formula. In my case it works. Appreciate if you show what's wrong in it, will be useful. 

Hi Sergei,

Looking back at my post @ 3:30pm above, I tried using several variations that didn't work but not sure if I was using them correctly. Can you reply with the exact formula you are referring to, then I can copy and paste it to know for sure?

The formula that is now in this cell now (I think this is the one as there were a few cells I was getting help with) is the following formula...

=IFERROR(SUM(S33,AC33,AM33,AW33,BG33,BQ33,CA33)/INDEX(FREQUENCY((S33,AC33,AM33,AW33,BG33,BQ33,CA33),0),2),0)

Thank you.

@Nickerz_2020 

Sorry, have no idea on which post you give reference as "my post @ 3:30pm above,".  Site shows my local time which is most probably not the same as for your timezone.

As for the formula you may find it in the file attached to my previous post.

1 best response

Accepted Solutions
best response confirmed by Nickerz_2020 (Brass Contributor)
Solution
Do you have the AverageIf function?

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

View solution in original post