| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| using a vlookup, can i use isblank to show an empty cell | Excel Worksheet Functions | |||
| Show a blank result in a cell when there is no value in the "Lookup" cell | New Users to Excel | |||
| show 0.00 if referenced cell is empty | Excel Worksheet Functions | |||
| Using if function to show blank when cell in other sheet is empty | Excel Worksheet Functions | |||
| Need cells to show as zero or empty | Excel Worksheet Functions | |||
Linear Mode
