'how to find the percentage between 2 numbers with 2 conditions?
I'm having trouble finding the formula for percentages I have to calculate the employee bonus e.g. on April 8, 2022, we have 90.13% of the standard developed, the same on 04/04/2022 is 108.54% where 90% is equal to 3.75% of the bonus and a maximum of 120% is equal to 15% of the bonus below 90% of the worked norm, the employee will not receive a bonus
I need to find a formula that will calculate how much the employee's bonus will be
89.99% = 0%
90% = 3.75%
90.13% = ???
108.54% = ???
120% = 15%
130% = 15%
140% = 15%
150% = 15%
Anyone can help me with this ??
Solution 1:[1]
My browser won't view the image for some reason, so in this answer I'm assuming that the 'Percentage of standard developed' is column A, please change cell references to suit.
in Cell B2 (or a different column) enter the formula;
=IFS(A2<90%, 0, A2>120%, 15%, TRUE, A2*37.5%-30%)
Solution 2:[2]
Here is a dynamic formula for covering the range you specified. It will help you understand how to come up with the numbers. Given the conditional formatting, I think you already know when not to apply it.
=$A2+(B1-$A1)*($E2-$A2)/($E1-$A1)
Basically you start with the lower bound for the percentage 3,75 then you calculate how many steps you took from 90 and multiply the number of steps by the ratio of step range (120 - 90) to the percentage range (15 - 3,75).
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Spencer Barnes |
| Solution 2 |