Archive of Mr Excel Message Board
Back to Dates in Excel archive index
Back to archive home
Cell returning #N/A. How can I change formula?
Posted by Gerald Marshall on November 13, 2001 12:42 PM
If I enter the number 3.5 or 3.25 in cell A1 I want cell B1 to return .50. If I enter 1 in cell A1 I want cell B1 to return 1.
looking at the answer to a similar query some days ago I have entered the formula =VLOOKUP(A1,{3.75,0.5;3.25,0.5;1,1},2,0).
This works perfectly when a number is entered in A1, however when A1 is left blank Cell B1 returns #N/A. What can I add to the above formula to leave cell B1 blank when no number is entered in A1.
Any help appreciated
Gerald Marshall
Re: Cell returning #N/A. How can I change formula?
Posted by Juan Pablo on November 13, 2001 12:45 PM
You can add this:
=IF(LEN(A1),VLOOKUP(...),"")
Juan Pablo
=IF(ISNUMBER(A1),VLOOKUP(A1,{3.75,0.5;3.25,0.5;1,1},2,0),"") [NT]
Posted by Aladin Akyurek on November 13, 2001 12:45 PM
Re: Cell returning #N/A. How can I change formula?
Posted by Galto on November 13, 2001 12:49 PM
Hi,
You could try a nested IF using a blank value "" ...
=if(a1="","",VLOOKUP(A1,{3.75,0.5;3.25,0.5;1,1},2,0))
Checks out on my system...
-Galto
Re: Cell returning #N/A.Thanks a Million,They all work perfectly!!
Posted by Gerald Marshall on November 13, 2001 12:59 PM
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our
online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.