nested IF calculation

**ssglick** · 11-06-2011, 01:53 AM

I am trying to make a formula in column D that tests a column of data points (A) against a range (B) and then uses a multiplier (C) to get a result while adding to it ranges/results below the value of that data point. For example attached in file Book2.xls, the data point 15.34 is between 11.674 and 17.96. Therefore the answer I want is (8.98*.122) + (11.674-8.98)*.139 + (15.34-11.674)*.302 = 2.577. For the data point 6.32, then it is simply 6.32*.122= .771. I think I need to use nested IF's but not sure how?

**DonkeyOte** · 11-06-2011, 04:10 AM

For multi tiered calculations like this a table with incremental rates allows for standard SUMPRODUCT based approach.

Your Range values should become the "low" point (rather than the high) and it would make sense to store the marginal multiplier for sake of simplicity.

Given modifications required to your original file I have posted a further sample with proposed solution in place.