Getting an average from multiple cells excluding "0"/Zeros, Microsoft Excel 2010

[HBsOnly]

Hey how's everyone doing? First time on this forum, I normally resist the urge to go on a forum and try to figure things out myself, but this time I just don't have a clue whats going wrong. Hopefully you guys and gals can help.

I am trying to calculate the average of the following cells for a template I am creating.

B23,B42,B62,B81 and B107 (all of these cells contain the =sum function)

However I know sometimes on certain weeks one or two of these particular cells will not see any activity and the sum will equal "0" zero.

So on a normal week
B23+B42+B62+B81+B107/5 = Average

I want a function where if lets say B23 = 0 it is automatically excluded from the calculation so it would look like this:
B42+B62+B81+B107/4 = Average

I tried plugging this function in:

=SUM(B23,B42,B62,B81,B107)/INDEX(FREQUENCY((B23,B42,B62,B81,B107),{0}),2)

Excel gives me : #DIV/0!

Then I tried,

=IF(INDEX(FREQUENCY(B23,B42,B62,B81,B107),{0}),2),SUM(B23,B42,B62,B81,B107)/INDEX(FREQUENCY((B23,B42,B62,B81,B107),{0}),2),"")

Excel tells me : You've entered too many arguments for this function.


Would greatly appreciate it if someone could help thanks.

 

[JoeMo]

Try:

=IFERROR(SUM(B23,B42,B62,B81,B107)/(5-SUMPRODUCT((B23=0)+(B42=0)+(B62=0)+(B81=0)+(B107=0))),"All are 0")

 

[LxQ]

how about this:

=AVERAGEA(B23,B42,B62,B81,B107)

Edit: never mind, this only works if one is actually missing, not when it's 0

 

[par60056]

Welcome to the forum.

Try this:
=SUM(B23,B42,B62,B81,B107)/(COUNT(B23,B42,B62,B81,B107)-FREQUENCY((B23,B42,B62,B81,B107),{0}))

 

[par60056]

Averagea includes the zero cell. If 2 of the 5 values are zero they want to divide by 3 not 5.

 

[HBsOnly]

Just tried it, works great. Thanks for the help. Definitely a Mr.Excel MVP.

Try:

=IFERROR(SUM(B23,B42,B62,B81,B107)/(5-SUMPRODUCT((B23=0)+(B42=0)+(B62=0)+(B81=0)+(B107=0))),"All are 0")

 

[JoeMo]

Just tried it, works great. Thanks for the help. Definitely a Mr.Excel MVP.

You're welcome - thanks for the feedback.