# Multiple nested IF Statements

#### MMarks

##### New Member
I am trying to write the following IF statement
=IF(AW8>E8, "+", IF(AW8<E8, "-", IF(AW8=E8, "Y")))

However AW8 also contains a nested IF statement (See below), and therefore the arguments above are not showing correctly.
=IF(AV8<=19.99,"0",IF(AV8<=34. 5,"1",IF(AV8<49,"2",IF(AV8<63. 5,"3",IF(AV8<77,"4",IF(AV8>78, "5"))))))

Is there a way to resolve this?

Thanks

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### arunsjain

##### Board Regular
Hi MMarks,

Try this one

=IF(AV8<=19.99,"0",IF(AV8<=34.5,"1",IF(AV8<49,"2",IF(AV8<63.5,"3",IF(AV8<77,"4","5")))))

Because if AV8 = 78 then what will be the value, not defined.

Last edited:

#### MMarks

##### New Member
The AV8 statement works fine with or without the =78.
It is the AW8 statement that won't work.
If I change AV8 to a number rather than the nested IF, then the AW8 statement works.
In cell AW8 I have the AV8 statement. In cell AX8 I have the AW8 statement.
It doesn't seem to work as AX8 (=IF(AW8>E8, "+", IF(AW8>E8, "-", IF(AW8=E8, "Y")))
Refers to the nested IF in cell AW8

#### arunsjain

##### Board Regular
Use following formula, it will work

=IF(AV8<=19.99,0,IF(AV8<=34.5,1,IF(AV8<49,2,IF(AV8<63.5,3,IF(AV8<77,4,5)))))

=IF(AV8<=19.99,0,IF(AV8<=34. 5,1,IF(AV8<49,2,IF(AV8<63. 5,3,IF(AV8<77,4,IF(AV8>78, 5))))))

Need to remove " "..

Last edited:

#### arunsjain

##### Board Regular
Your formula in AX8 (=IF(AW8>E8, "+", IF(AW8>E8, "-", IF(AW8=E8, "Y"))), it should be (=IF(AW8>E8, "+", IF(AW8<E8, "-", IF(AW8=E8, "Y"))) or (=IF(AW8<E8, "+", IF(AW8>E8, "-", IF(AW8=E8, "Y")))

##### MrExcel MVP
Try to invoke an equivalent LOOKUP instead of a set of nested IFs.

=LOOKUP(AW8,{0,19.99,34.5,49,63.5,77,78},{0,0,1,2,3,4,5})

#### arunsjain

##### Board Regular
what if value is in between 34.5 and 49? Lookup function will not provide 2 in result.

#### MMarks

##### New Member
Formula error was a typo...

#### arunsjain

##### Board Regular
Is it working now?

#### MMarks

##### New Member
No. AX8 only shows + in all cases....

Replies
5
Views
153
Replies
1
Views
171
Replies
6
Views
531
Replies
6
Views
426
Replies
0
Views
483

1,191,287
Messages
5,985,762
Members
439,981
Latest member
ofori francis

### 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.

### Which adblocker are you using?

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

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