Combining 3 Nested If Statements Into One Cell?

**UBSmokie** · 11-17-2009, 03:22 PM

Hello -

I am trying to do a pricing worksheet that is based off of three different cost scenarios. Depending on the values of two variables (B6 -> Budget & B7 -> Volume), one of the three cost scenarios is applied and each scenario is tiered based on the variables. I can get this to work across three separate cells and each of the formulas below work individually, however, I'd like to combine this into one cell for ease of use/end user clarity. Is the below situation possible?

Cost Scenario 1
- Use only if B7 is between 50 and 125, and B6 is less than 5,000
- IF(B7>='Management - Fee Based'!A19,B7/'Management - Fee Based'!K19,IF(B7>'Management - Fee Based'!A20,B7/'Management - Fee Based'!K20,IF(B7>'Management - Fee Based'!A21,B7/'Management - Fee Based'!K21,IF(B7>49,B7/'Management - Fee Based'!K22,5))))

Cost Scenario 2
- Use only if B7 is greater than 125 but less than 5,000 and B6 is greater than 5,000
- IF(AND(B6>'Management - Fee Based'!A11,B7>124,B7<5000),B6/'Management - Fee Based'!K11,IF(AND(B6>'Management - Fee Based'!A12,B7>124,B7<5000),B6/'Management - Fee Based'!K12,IF(AND(B6>'Management - Fee Based'!A13,B7>124,B7<5000),B6/'Management - Fee Based'!K13,IF(AND(B6>'Management - Fee Based'!A14,B7>124,B7<5000),B6/'Management - Fee Based'!K14,IF(AND(B6>'Management - Fee Based'!A14,B7>124,B7<5000),B6/'Management - Fee Based'!K14,IF(AND(B6>'Management - Fee Based'!A15,B7>124,B7<5000),B6/'Management - Fee Based'!K15,5))))))

Cost Scenario 3
- Use only if B6 and B7 are greater than 5,000
- IF(AND(B6>5000,B7>'Management - Fee Based'!A26),B7/'Management - Fee Based'!K26,IF(AND(B6>5000,B7>'Management - Fee Based'!A27),B7/'Management - Fee Based'!K27,IF(AND(B6>5000,B7>'Management - Fee Based'!A28),B7/'Management - Fee Based'!K28,IF(AND(B6>5000,B7>'Management - Fee Based'!A29),B7/'Management - Fee Based'!K29,IF(AND(B6>5000,B7>'Management - Fee Based'!A30),B7/'Management - Fee Based'!K30,5)))))

If more information is needed, of course ask. Thank you in advance for any insight!

**TommyHawk** · 11-17-2009, 05:03 PM

You could, perhaps, hide these 3 major calculations (or make the font white) and have a separate "user friendly cell" reference one of the 3 cells based on the same criteria.

=if(scenario1 criteria, scenario 1 calc cell, if(scenario 2 criteria, scenario 2 calc cell, if(scenario 3 criteria, scenario 3 cal cell)))

That way, the 3 calculations themsleves are easier to manipulate/fix individually if needed, and the user can focus on (or only see) the ultimate result.

**JBeaucaire** · 11-17-2009, 05:34 PM

You're going about this the wrong way. You never evaluate the same cell over and over again with IF statements. That's what LOOKUP formulas are for.

LOOKUP()
VLOOKUP()
INDEX/MATCH()

I look at your three monster formulas and wonder if this would work:

1st:
=VLOOKUP(B7,'Management - Fee Based'!$A$19:$K$22,11,TRUE)
or
=LOOKUP(B7,'Management - Fee Based'!$A$19:$A$22,'Management - Fee Based'!$K$19:$K$22)

2nd:
=VLOOKUP(B7,'Management - Fee Based'!$A$11:$K$15,11,TRUE)
or
=LOOKUP(B7,'Management - Fee Based'!$A$11:$A$15,'Management - Fee Based'!$K$11:$K$15)

3rd:
=VLOOKUP(B7,'Management - Fee Based'!$A$26:$K$30,11,TRUE)
or
=LOOKUP(B7,'Management - Fee Based'!$A$26:$A$30,'Management - Fee Based'!$K$26:$K$30)

Try it out. Press F1 and read up on VLOOKUP and see if you can tweak those formulas into working.

If you post up a sample workbook so we can see this data layout and your data on the Management - Fee Based sheet, we might be able to simplify this down quite a ways for you.