Need formula to avg. 3 #'s with out recognizing zero

[johno68]

I know my way around excel pretty well, but I can't figure out how t
write a formula that will allow me to average 3 numbers but no
recognize zero. Just turning off zero values doesn't work because th
zero is still counted. The formula I am looking for should return th
following answers in each of the following examples:

4,6,8 = 6
5,0,3 = 4
2,0,0 = 2
0,0,0 = 0

Joh

 

[William]

Hi

The formula should be entered as an array (hold down Ctrl|Shift|Enter
simultaneously)
{=AVERAGE(IF(B5:B7<>0,B5:B7))}

--
XL2002
Regards

William

(e-mail address removed)

| I know my way around excel pretty well, but I can't figure out how to
| write a formula that will allow me to average 3 numbers but not
| recognize zero. Just turning off zero values doesn't work because the
| zero is still counted. The formula I am looking for should return the
| following answers in each of the following examples:
|
| 4,6,8 = 6
| 5,0,3 = 4
| 2,0,0 = 2
| 0,0,0 = 0
|
| John
|
|
| ---
| Message posted
|

 

[CLR]

=IF(SUM(L1:N1)=0,0,SUM(L1:N1)/COUNTIF(L1:N1,">0"))

Vaya con Dios,
Chuck, CABGx3