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.
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.