Problem with a formula

minette

Board Regular
Joined
Jul 8, 2005
Messages
237
Hi all,

I have the following formulas and text in cells B1 to E1

in (B1) NR12 7ER
in (C1) =IF(ISERROR(E2),"0",IF(E2>=1,E2,"OK"))
in (D1) =IF(C2="0","1.00",IF(C2=1,"1.20",IF(C2=2,"Refer EA",IF(C2=3,"Decline",""))))
in (E1) =VLOOKUP(A1,'Flood'!A:B,2,FALSE)

The description of these cells are as follows:-
in (B1) the user type in a postcode
in (C1) checking cell E1 and put in criteria based on whether an error is returned.
in (D1) this formula allocates text based on the value in cell C1
in (E1) this cell is looking at sheet "Flood" to lookup the number against the postcode specified in cell B1.

The answers returned are as follows:
in (C1) 3
in (D1) (empty)
in (E1) 3

However, D1 should be "Decline" and not be empty.

I wonder if someone has any ideas of why this formula does not work. I have a hunch that it has something to do with referring to formulas and not text etc, etc, but I need an expert to help me get to the bottom of this one.

Thanks to everyone who's having a go.

Minette
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Remove the quotes from your numerics -

in (C1) =IF(ISERROR(E2),0,IF(E2>=1,E2,"OK"))
in (D1) =IF(C2=0,1,IF(C2=1,1.20,IF(C2=2,"Refer EA",IF(C2=3,"Decline",""))))
 

minette

Board Regular
Joined
Jul 8, 2005
Messages
237
Thanks for your very quick response - unfortunately, it did not work. Do you have any other ideas?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Seems like Jon's suggestion would be sensible but it looks like the problem is that your VLOOKUP returns a text value, so either change the "numbers" in Flood!B:B to real numbers by selecting that column and using

Data > Text to columns > Finish

...or change your VLOOKUP formula to

=VLOOKUP(A1,'Flood'!A:B,2,FALSE)+0
 

minette

Board Regular
Joined
Jul 8, 2005
Messages
237
Hi Barry -I changed the vlookup formula as you suggested, and it WORKS great. Thanks very much for your help.

Minette
 

Forum statistics

Threads
1,141,734
Messages
5,708,162
Members
421,549
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top