Dear All,
I'm struggling with Sumproduct formula to calculate bonus based on percentage budget achieved (see below).
My desired amount Column U and the formula I'm trying to implement in Column T.
If % Budget achieved is 100% then you expect 7.5% Bonus, $7,500 (100,000*7.5%). If you achieved 101% then you expect a bonus higher 7.5k,
around $7,650 (see cell U9), but unfortunately can't make formula work in cell T9.
<tbody>
</tbody>
<tbody>
</tbody>
Your help would be greatly appreciated.
Kind Regards
Biz
I'm struggling with Sumproduct formula to calculate bonus based on percentage budget achieved (see below).
My desired amount Column U and the formula I'm trying to implement in Column T.
If % Budget achieved is 100% then you expect 7.5% Bonus, $7,500 (100,000*7.5%). If you achieved 101% then you expect a bonus higher 7.5k,
around $7,650 (see cell U9), but unfortunately can't make formula work in cell T9.
R | S | T | U | V | W | X | Y | Z | |
4 | Percentage of Budget Achieved | Payout % | Rate Diff % | ||||||
5 | |||||||||
6 | Amount | % Achieved | Payout $ | Desired Amount | Var | - | - | - | |
7 | 100,000 | 91% | - | 750.00 | 750.00 | 90% | - | - | |
8 | 100,000 | 92% | 689.99 | 1,500.00 | 810.01 | 91% | 0.75% | 0.75% | |
9 | 100,000 | 101% | 7,574.93 | 7,650.00 | 75.07 | 92% | 1.50% | 0.75% | |
10 | 100,000 | 102% | 7,649.93 | 7,800.00 | 150.07 | 93% | 2.25% | 0.75% | |
11 | 100,000 | 111% | 9,989.90 | 9,150.00 | - 839.90 | 94% | 3.00% | 0.75% | |
12 | 100,000 | 112% | 10,079.90 | 9,300.00 | - 779.90 | 95% | 3.75% | 0.75% | |
13 | 100,000 | 113% | 10,169.90 | 9,450.00 | - 719.90 | 96% | 4.50% | 0.75% | |
14 | 100,000 | 114% | 10,259.90 | 9,600.00 | - 659.90 | 97% | 5.25% | 0.75% | |
15 | 100,000 | 115% | 10,349.90 | 9,750.00 | - 599.90 | 98% | 6.00% | 0.75% | |
16 | 100,000 | 116% | 10,439.90 | 9,900.00 | - 539.90 | 99% | 6.75% | 0.75% | |
17 | 100,000 | 117% | 10,529.90 | 10,050.00 | - 479.90 | 100% | 7.50% | 0.75% | |
18 | 100,000 | 118% | 10,619.90 | 10,200.00 | - 419.90 | 110% | 9.00% | 1.50% | |
19 | 100,000 | 119% | 10,709.90 | 10,350.00 | - 359.90 | 120% | 10.50% | 1.50% | |
20 | 100,000 | 120% | 10,799.89 | 10,500.00 | - 299.89 | 130% | 12.00% | 1.50% | |
21 | 50 | 91% | - | 0.38 | 0.38 | 140% | 13.50% | 1.50% | |
22 | 50 | 102% | 3.75 | 3.90 | 0.15 | 150% | 15.00% | 1.50% |
<tbody>
</tbody>
Formeln der Tabelle | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
<tbody> </tbody> |
<tbody>
</tbody>
Your help would be greatly appreciated.
Kind Regards
Biz