How to manage a large nested if?

[xdennis]

Example.xlsx


Hello,
I have a problem which is difficult to me.

I'm trying to identify a specific Rate.

Please review the attachement, it might be easier to see the logic in the spreadsheet then for me to type it out.

I'm trying to identify a way to reduce a large nested if statement down. Maybe use some creative vlookup w/ pivot? I'm stuck and don't know where to begin.

Thanks!

Dennis

[Teethless mama]

Try to set up a lookup table, then use combination of INDEX & MATCH functions.

[djapigo]

Nice name...

I used a helper column and used an array-formula... make sure to use an array-enter (CTRL-SHIFT-ENTER) and not just ENTER...

Here's my attached spreadsheet...

Hope this helps,
Dennis

[FDibbins]

@ mama, if you had looked atthe upload, that is pretty much what the OP has?

[Teethless mama]

@ mama, if you had looked atthe upload, that is pretty much what the OP has?

I did see the upload, and the table wasn't setup proper way to use with INDEX & MATCH function

[FDibbins]

@ mama - then maybe advise the OP on how it should look, rather than just a comment like "Try to set up a lookup table, then use combination of INDEX & MATCH functions. "?

[xdennis]

Plan example_2.xlsx

Hey Dennis, Thanks so much!

I see you code works perfectly, although I wish I had a better understanding of what it's doing. I never knew of the Array (Ctrl Shift Enter) trick. I had to youtube it for an understanding This trick will come in handy when calculating product pricing for sure!


Now, I plugged it into a real life example (Attached) and although it appears to be "close" some calculations are still off and I can't figure out why.

Thanks again for your assistance!

[djapigo]

Hi xdennis,

Your helper column should be your range...

So, row 2 should be 0 and 104
row 3 should be 104 to 234
row4 should be 234 to 999999
row 5 should be 0 to 104
etc.

You can use this formula in A2: =IF(C1=C2,B1,0)
Copy this formula down for your helper column

Hopefully this will fix your problem...

[xdennis]

Ah ha! It all makes sense now.

For some reason I was not "seeing" the range. Now I see the helper column makes the range.

Thanks again, Dennis!