I would need to write a formula that looks at cell with total cost value of $17812.61 and then references this table and points to 6.5%, what is the best way to achieve this? I was thinking an if formula might be too long as it checks for each percentage scenario but would appreciate any sugegstions.
thank you,
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
thank you,
Rate Range | Annual Collections | Monthly Collections | |
11.00% | 362,714.07 | 30,226.17 | |
10.75% | 354,470.57 | 29,539.21 | |
10.50% | 346,227.07 | 28,852.26 | |
10.25% | 337,983.57 | 28,165.30 | |
10.00% | 329,740.07 | 27,478.34 | |
9.75% | 321,496.57 | 26,791.38 | |
9.50% | 313,253.06 | 26,104.42 | |
9.25% | 305,009.56 | 25,417.46 | |
9.00% | 296,766.06 | 24,730.51 | |
8.75% | 288,522.56 | 24,043.55 | |
8.50% | 280,279.06 | 23,356.59 | |
8.25% | 272,035.56 | 22,669.63 | |
8.00% | 263,792.05 | 21,982.67 | |
7.75% | 255,548.55 | 21,295.71 | |
7.50% | 247,305.05 | 20,608.75 | |
7.25% | 239,061.55 | 19,921.80 | |
7.00% | 230,818.05 | 19,234.84 | |
6.75% | 222,574.55 | 18,547.88 | |
6.50% | 214,331.04 | 17,860.92 | |
6.25% | 206,087.54 | 17,173.96 | |
6.00% | 197,844.04 | 16,487.00 | |
5.75% | 189,600.54 | 15,800.04 | |
5.50% | 181,357.04 | 15,113.09 | |
5.25% | 173,113.54 | 14,426.13 | |
5.00% | 164,870.03 | 13,739.17 | |
4.75% | 156,626.53 | 13,052.21 | |
4.50% | 148,383.03 | 12,365.25 | |
4.25% | 140,139.53 | 11,678.29 | |
4.00% | 131,896.03 | 10,991.34 | |
3.75% | 123,652.53 | 10,304.38 | |
3.50% | 115,409.02 | 9,617.42 | |
3.25% | 107,165.52 | 8,930.46 | |
3.00% | 98,922.02 | 8,243.50 | |
2.75% | 90,678.52 | 7,556.54 | |
2.50% | 82,435.02 | 6,869.58 | |
2.25% | 74,191.52 | 6,182.63 | |
2.00% | 65,948.01 | 5,495.67 |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>