Lookup to Show Empty Cell

  • Thread starter Thread starter RoadKill
  • Start date Start date
R

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
 
Sum the values and divide by a countif

So it would be like

Sum(B2:B51)/Countif(B2:B51,">0")
 
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
 
Intercept the zero:

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

AVERAGE() as you noted will ignore zeros.
 
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
 
Back
Top