Select Different Formulas if Within Multiple Value Ranges/Thresholds

[DrSues02]

Hi, trying to find a more elegant formula without resorting to a very very long nested IF statement...

See attached.

Reference price is in A1. Set of value ranges in A7-B11. Different formulas are calculated if the reference price is within the range of values with an output calculation in A3.

Want to be able to enter any price in A1, have the excel formula find which low/high range it falls in, then use the correct formula from the options in A3 to return the correct math calculation.

To note: it's inclusive, 70 would be included in the first row of calculations not the second.

[protonLeah]

I think your range table setup is wrong. Having a price in both the high and low columns is ambiguous.

=((B1-B2)*LOOKUP($B$1,$A$7:$A$11,C7:C11))-LOOKUP($B$1,$A$7:$A$11,D7:D11)