Nested if staements

[jdissi]

I have a spreadsheet where I need to create a nested if statement. THe first condition is if sales are greater than or equal to 30000 the truth is 3000, if greater than or equal to 40000 than 4000, if greater than or equal to 50000 than 5000 and if greater than or equal to 60000 than 6000. Thanks.

 

[Trebor76]

Hi jdissi,

Welcome to MrExcel!!

Though you can nest the statements, the LOOKUP function is better suited in this case, i.e. the following formula...

=LOOKUP(A1,{0,30000,40000,50000,60000},{0,3000,4000,5000,6000})

...will return the desired results based on the value in cell A1 (change to suit).

HTH

Robert

 

[jdissi]

Thanks Robert.

I was hoping to getting the nested if formula to see what I was doing wrong. My formula is returning an error message.

 

[Trebor76]

OK, this should do it:

=IF(ISNUMBER(A1)=FALSE,0,IF(A1<30000,0,IF(AND(A1>=30000,A1<=39999),3000,IF(AND(A1>=40000,A1<=49999),4000,IF(AND(A1>=50000,A1<=59999),5000,6000)))))

HTH

Robert