Lookup to Show Empty Cell
 

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

Lookup to Show Empty Cell
 

Sum the values and divide by a countif

So it would be like

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

"RoadKill" wrote:

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

Lookup to Show Empty Cell
 

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" wrote in message
...
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

Lookup to Show Empty Cell
 

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

"Pete_UK" wrote:

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" wrote in message
...
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

Lookup to Show Empty Cell
 

Intercept the zero:

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

AVERAGE() as you noted will ignore zeros.
--
Gary''s Student - gsnu200776


"RoadKill" wrote:

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

"Pete_UK" wrote:

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" wrote in message
...
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

Lookup to Show Empty Cell
 

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

"RoadKill" wrote in message
...
Actually I tried that first, and it still gave me the zero.

"Pete_UK" wrote:

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" wrote in message
...
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