nested if statement- trying to avoid but don't know how

**dumpster1985** · 01-06-2012, 03:15 PM

Okay,

I have another formula issue. I am trying to write a formula to tell if the rate is "Above", "Exact", or "Below" a certain rate. The issue is there are three different variables to use to determine: Amount (has 6 different ranges), rating (could be 1-6), and the rate itself. Below is an attempt for a formula that ended up being a nested if statement which doesn't work. I have also attached the worksheet so you can see what I am working on. Any suggestions on writing this formula? I am not too excel savy, so you might need to explain a little. Thanks so much!

=IF((A4=1)*AND(2501000>=C4)*AND(B4<(MAX('Rate Matrix'!E10:F10))),"Below",(IF((A4=1)*AND(2501000>=C4)*AND(B4=(MAX('Rate Matrix'!E10:'Rate Matrix'!F10))),"Exact",(IF((A4=1)*AND(2501000>=C4)*AND(B4>(MAX('Rate Matrix'!E10:F10))),"Above",(IF((A4=1)*AND(2501000>C4>=100100)*AND(B4<(MAX('Rate Matrix'!E9:F9))),"Below",(IF((A4=1)*AND(2501000>C4>=100100)*AND(B4=(MAX('Rate Matrix'!E9:F9))),"Exact",(IF((A4=1)*AND(2501000>C4>=100100)*AND(B4>(MAX('Rate Matrix'!E9:F9))),"Above",)))))))))))

Test Pricing Sheet.xlsx

**dilipandey** · 01-06-2012, 03:29 PM

Hi dumpster,

Why you are not using col D i.e, amount category in your formula. Also would like to say that you are checking too many things in your formula and if not then needs to be checked. I believe logic should be simplified, else use col D to cut down the data search on another sheet.

Regards,
DILIPandey

<click on below 'star' if this helps>

**dumpster1985** · 01-06-2012, 04:55 PM

I could use column D but I still need to check the other sheet for the rates to compare it against the greater of the two other rates. The rates I am comparing against will change regularly, so I need a formula to allow me to change them without issues. Thoughts?

**dilipandey** · 01-07-2012, 05:08 AM

Hi dumpster,

See the attached file where I have given suggestion plus query.

Regards,
DILIPandey

<click on below 'star' if this helps>

**rscsmith** · 01-09-2012, 02:56 AM

Hi there

I think I've got this working. To make my version work using your data structure, I had to introduce column C on the Rate Matrix sheet. This is used to look up the amount of the loan.

The function works using INDEX and MATCH.

It works out which row it is looking at using the MATCH statement. It works out which column using the Risk Rating and multiplying by 3 (as there are 3 columns for each risk rating).

Sample attached.

Regards, Rob.