All,
I am trying to find the best way to get the correct answer from the below table. Basically the answer will depend on 2 things:
I have two cells in another sheet, one states the percentage, and the other the amount. What I need is an easy way to get if cell F17 falls into one if the below percentage ranges, and cell F16 is between any of those amounts, then calculate the answer.
EG. F17 = 83.4% & F16 = 570,000. The answer would be 570,000*0.801= 4,565.70
<tbody>
</tbody>
So basically if have tried the IF/AND combination, and while it works, it's a huge to maintain, and if the boss changes the percentage amounts, or adds more by making the ranges smaller, it kills the formula.
Just seeing if there is a better way.
Regards
Oz
I am trying to find the best way to get the correct answer from the below table. Basically the answer will depend on 2 things:
- The percentage range in A & B
- The value the product is between.
I have two cells in another sheet, one states the percentage, and the other the amount. What I need is an easy way to get if cell F17 falls into one if the below percentage ranges, and cell F16 is between any of those amounts, then calculate the answer.
EG. F17 = 83.4% & F16 = 570,000. The answer would be 570,000*0.801= 4,565.70
| 500,000 | 600,000 | 750,000 | ||
| 80.01% | 81.00% | 0.540% | 0.894% | 0.931% |
| 81.01% | 82.00% | 0.540% | 0.894% | 0.931% |
| 82.01% | 83.00% | 0.568% | 0.922% | 1.071% |
| 83.01% | 84.00% | 0.801% | 0.922% | 1.071% |
| 84.01% | 85.00% | 0.857% | 1.146% | 1.323% |
| 85.01% | 86.00% | 0.997% | 1.146% | 1.323% |
| 86.01% | 87.00% | 1.006% | 1.388% | 1.602% |
| 87.01% | 88.00% | 1.267% | 1.388% | 1.602% |
| 88.01% | 89.00% | 1.369% | 1.919% | 2.180% |
| 89.01% | 90.00% | 1.723% | 1.919% | 2.180% |
| 90.01% | 91.00% | 2.581% | 3.028% | 3.578% |
| 91.01% | 92.00% | 2.637% | 3.028% | 3.578% |
| 92.01% | 93.00% | 2.981% | 3.401% | 3.783% |
| 93.01% | 94.00% | 2.981% | 3.401% | 4.025% |
| 94.01% | 95.00% | 3.298% | 3.401% | 4.370% |
<tbody>
</tbody>
So basically if have tried the IF/AND combination, and while it works, it's a huge to maintain, and if the boss changes the percentage amounts, or adds more by making the ranges smaller, it kills the formula.
Just seeing if there is a better way.
Regards
Oz
