I am looking for some assistance. I have two values, 1. Volume 2. Term. I would like to create a formula that for each row with these values i can look up the correct discount from the discount table, which is as follows:
Untitled Spreadsheet
I need the result to take the volume and term values in a different table, establish which volume range row is applicable and then select the appropriate discount from the appropriate Term column.
All and any help gratefully received.
J
Untitled Spreadsheet
| Volume | Term Discount (Years) | |||
| From | to | 1 | 2 | 3 |
| 1 | 10 | 0.0% | 5.0% | 7.5% |
| 11 | 20 | 5.0% | 7.5% | 10.0% |
| 21 | 30 | 10.0% | 12.5% | 15.0% |
| 31 | 40 | 15.0% | 17.5% | 20.0% |
| 41 | 100 | 20.0% | 22.5% | 25.0% |
| 101 | 2000 | 25.0% | 27.5% | 30.0% |
I need the result to take the volume and term values in a different table, establish which volume range row is applicable and then select the appropriate discount from the appropriate Term column.
All and any help gratefully received.
J
