I have a tiered fee schedule and I want to create a formula to return the correct fee - why is my formula not working?
Book2 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Start | End | % | |||||
2 | $ - | $ 500,000.00 | 1.00% | |||||
3 | $ 500,000.00 | $ 1,000,000.00 | 0.75% | |||||
4 | $ 1,000,000.00 | $ 2,500,000.00 | 0.50% | |||||
5 | $ 2,500,000.00 | $ - | 0.25% | |||||
6 | ||||||||
7 | ||||||||
8 | Balance | Fee should be | Formula | |||||
9 | $ 104,916.96 | $ 1,049.16 | ($5,200.83) | =SUMPRODUCT(($A9<=$B$2:$B$5)*($A9>$A$2:$A$5)*($A9-$A$2:$A$5)*$C$2:$C$5)+SUMPRODUCT((($A9>$B$2:$B$5)*($B$2:$B$5-$A$2:$A$5))*$C$2:$C$5) | ||||
10 | $ 2,780,964.40 | $ 16,952.40 | $10,000.00 | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D9:D10 | D9 | =SUMPRODUCT(($A9<=$B$2:$B$5)*($A9>$A$2:$A$5)*($A9-$A$2:$A$5)*$C$2:$C$5)+SUMPRODUCT((($A9>$B$2:$B$5)*($B$2:$B$5-$A$2:$A$5))*$C$2:$C$5) |