Excel: Median if <>0

[krets]

This formula is giving me a #VALUE! error:

{=MEDIAN(IF((B3,E3,H3,K3,N3,Q3)<>0,(B3,E3,H3,K3,N3,Q3)))}

(Yes, I did Shft + Ctrl + Enter to create the array formula.)

Can I not use that like I would use:

{=MEDIAN(IF(C3:C33<>0,C3:C33))}

If not, how can I perform the equivalent of finding the median of a specific set of cells, not a range?

 

[SkipVought]

Hi,

Maybe...
[tt]
{=MEDIAN(IF(C3:C33<>0,C3:C33,0))}
[/tt]
???

Skip,

_[red][/red]

 

[krets]

This: {=MEDIAN(IF(C3:C33<>0,C3:C33))} works fine.

It's this one that's giving me the trouble:

{=MEDIAN(IF((B3,E3,H3,K3,N3,Q3)<>0,(B3,E3,H3,K3,N3,Q3)))}

I tried:

{=MEDIAN(IF((B3,E3,H3,K3,N3,Q3)<>0,(B3,E3,H3,K3,N3,Q3),0))}

But that doesn't work either.

 

[yogia]

It's this one that's giving me the trouble:

{=MEDIAN(IF((B3,E3,H3,K3,N3,Q3)<>0,(B3,E3,H3,K3,N3,Q3)))}

Hi krets:

Let us try ...

=MEDIAN(IF(MOD(COLUMN(B3:Q3)+1,3)=0,B3:Q3,""))

I hope this helps!


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[xlhelp]

I could be mistaken, but I remember reading somewhere that B3, E3, etc may not constitue an array. Whereas 3,5,7 etc does.

May be someone can shed more light.

Member- AAAA Association Against Acronym Abusers