averaging time values excluding zero

[Karen]

I received a great solution to a previous question by
using an array formula:=AVERAGE(IF(C12:G12<>0,C12:G12))
However how can I average a monthly total excluding zeros
when the fields are not in a row. For instance how could
I convert the following formula to exclude zero values?
=AVERAGE(AP43,AG43,X43,O43,F43)

Thanks!

 

[Juan Sanchez]

Karen, I'm not sure but I think you will need a User
Defined Function for that... I have one, see if it works
'==========================================
Function NoZeroAverage(MyRange) As Single
For Each MyRange In MyRange
If MyRange <> 0 Then B = B + MyRange
If MyRange <> 0 Then A = A + 1
Next MyRange
If B = 0 Then
NoZeroAverage = 0
Else
NoZeroAverage = B / A
End If
End Function
'==========================================

I tryed to use it like =NoZeroAverage(A1,B6,C8) but it
didn't work, however, if you name the range like this:

1.-Select the non-consecutive range (Hold CTRL down and
click on each of the cells in the range
i.e.:AP43,AG43,X43,O43,F43 and then go to INSERT > NAME >
DEFINE, and give a name to the range, i.e. MyRange then
use the formula like

=NoZeroAverage(MyRange), that worked for me....

If you are not familiar with UDF (User Defined Functions)
see:
http://www.exceltip.com/show_tip/Excel_Custom_Functions_usi
ng_VBA/Writing_Your_First_VBA_Function_in_Excel/631.html

Cheers
Juan

 

[Leo Heuser]

Karen

One way:

=SUM(AP43,AG43,X43,O43,F43)/((AP43<>0)+(AG43<>0)+(X43<>0)+(O43<>0)+(F43<>0))