 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008Lookup 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 Sum the values and divide by a countif So it would be like Sum(B2:B51)/Countif(B2:B51,">0") > 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 [quoted text clipped - 5 lines] > > Thank you 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 > Okay, I have my lookup to zero but now realize that in the future that > will [quoted text clipped - 8 lines] > > Thank you Actually I tried that first, and it still gave me the zero. > Instead of the zero in the middle, change it to "": > [quoted text clipped - 17 lines] > > > > Thank you Intercept the zero: =IF(your_formula=0,"",your_formula) AVERAGE() as you noted will ignore zeros.  SignatureGary''s Student - gsnu200776 > Actually I tried that first, and it still gave me the zero. > [quoted text clipped - 19 lines] > > > > > > Thank you 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 > Actually I tried that first, and it still gave me the zero. > [quoted text clipped - 20 lines] >> > >> > Thank you |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.