MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by Juan Pablo on November 13, 2001 12:45 PM

You can add this:

=IF(LEN(A1),VLOOKUP(...),"")

Juan Pablo

Posted by Aladin Akyurek on November 13, 2001 12:45 PM

=IF(ISNUMBER(A1),VLOOKUP(A1,{3.75,0.5;3.25,0.5;1,1},2,0),"") [NT]

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

Posted by Gerald Marshall on November 13, 2001 12:59 PM

Re: Cell returning #N/A.Thanks a Million,They all work perfectly!!