If statement help

[CROY1985]

I need an If statement that will be placed in column A. It will look at column B (a range of account numbers) and then name them accordingly.
Those account numbers falling between 100000 and 199999 inclusive need to be named "Sales". 200000 and 399999 need to be named "Direct Costs". anything else named "overheads".

The formula i am using keeps returning the false value no matter which number is in column b, and therefore all cells are being named overheads.

Here is what i have at the moment.

=IF(AND(B14>=100000,B14<=199999),"Sales",IF(AND(B14>=200000,B14<=399999),"Direct Costs", "Overheads"))

Can anyone correct my formula?

Thanks

 

[wigi]

The formula looks allright. What is the formatting of the cells in column B? Is it General, or rather Text?

 

[kidwispa]

You could always use this instead...

=IFERROR(LOOKUP(B14,{100000,200000,400000},{"Sales","Direct Costs","Overheads"}),"Overheads")

 

[CROY1985]

just tested it in a blank spreadsheet and it works.

Column B is part of an ms query table - so that must be the problem. Even though i have selected column b and formatted cells as number, it still has no bearing on the outcome of the if statement.

Any ideas on how i can work around this?

 

[CROY1985]

Some further info, if i cut and paste numbers from column b to a blank column i get the warning saying the numbers are stored as text. IS there another way of converting them - other than format cells, number?

 

[wigi]

Copy an empty cell, select column B cells, "paste special" and choose "Add".