Lookup to Show Empty Cell

[RoadKill]

Okay, I have my lookup to zero but now realize that in the future that will
mess up my averages if a person actually gets a zero. So how can I get this
to just show an empty cell as that doesn't appear to impact the averages like
a zero will.

=IF(ISNA(VLOOKUP($B4, '3-25'!$A$2:$C$51,2,0)),0,VLOOKUP($B4,
'3-25'!$A$2:$C$51,2,0))

Thank you

 

[akphidelt]

Sum the values and divide by a countif

So it would be like

Sum(B2:B51)/Countif(B2:B51,">0")

 

[Pete_UK]

Instead of the zero in the middle, change it to "":

=IF(ISNA(VLOOKUP($B4, '3-25'!$A$2:$C$51,2,0)),"" ,VLOOKUP($B4,
'3-25'!$A$2:$C$51,2,0))

Hope this helps.

Pete

 

[RoadKill]

Actually I tried that first, and it still gave me the zero.

 

[Gary''s Student]

Intercept the zero:

=IF(your_formula=0,"",your_formula)

AVERAGE() as you noted will ignore zeros.

 

[Pete_UK]

In that case you have found a match in your table, but the return value is 0
(the corresponding cell in the table could be ""). You could change your
formula to the following to overcome it:

=IF(ISNA(VLOOKUP($B4, '3-25'!$A$2:$C$51,2,0)),""
,IF(VLOOKUP($B4,'3-25'!$A$2:$C$51,2,0)=0,""
,VLOOKUP($B4,'3-25'!$A$2:$C$51,2,0)))

Hope this helps.

Pete