Nested If statement

[ukbiz]

Hi Guys, Any chance someone could look at this nested IF statement and see what the problem is. I have spent hours on it but it just doesn't seem to work.

Thanks in advance,

=IF(I18<49,7, IF(I18=50-100,11, IF(I18=101-150,12.5, IF(I18=151-501,I18/100*14, IF(I18=502-1001,I18/100*12, IF(I18=1002-5000,I18/100*10))))))

It is for delivery charges, so i'm looking at having

if cell I18 is less than 49 it should return 7
if cell I18 is less than 50-100 it should return 11
if cell I18 is less than 101-150 it should return 12.50
if cell I18 is less than 151-501 it should return 14% of Cell I18
if cell I18 is less than 502-1001 it should return 12% of cell I18
if cell I18 is less than 1002-5000 it should return 10% of cell I18.

The above formula just keeps returning 'FALSE'

 

[theBardd]

Try this

=IF(I18<=49,7,IF(I18<=100,11,IF(I18<=150,12.5,IF(I18<=501,I18/100*14,IF(I18<=1001,I18/100*12,IF(I18<=5000,I18/100*10))))))

 

[njimack]

=lookup(i18,{0,49,100,150,501,1001,5001},{7,11,12.5,0.14,0.12,0.1})*if(i18>150,i18,1)

 

[ukbiz]

Try this

=IF(I18<=49,7,IF(I18<=100,11,IF(I18<=150,12.5,IF(I18<=501,I18/100*14,IF(I18<=1001,I18/100*12,IF(I18<=5000,I18/100*10))))))

BOOM! Fantastic, thank you. Much appreciated.