Hey,
I'm creating a spreadsheet that will calculate bonus totals. I'm running into an issue with the math in the formulas.
We have a sliding scale that we use to determine that value of the contribution. For instance;
A target of 3 has been set as the "100% rated target". To acheive this goal, would net you a 100% bonus calculation in this field. If you netted a 5, you'd score 150%.
My issue comes in here.
If you achieved a number of "4", above the rating of "3=100%", you'd be getting a 125% bonus. However, if you just simply divide 4/3 to create a percentage in this way, you'd end up with 1.33 repeating.
I am trying to figure out how I would best represent a score of 4 over 3 as 1.25, and not as 1.33 repeating.
Is the target always 3? so 1 = 0.25 * 100
In the equations that are breaking, yes, I believe that is due to the fact it is an odd number.
The formulas that require an even number, I can successfully calculate 100% of the time.
Wouldn't something like this work?
=100+((X-3)*25)
where X is the number achieved
This gives you 25% at 0 though
If the scale is linear between 0 and 3 (so 1.5=50%), then this could be used:
=IF(X<3;(X/3)*100;100+((X-3)*25))
It seems like you're looking for something either nonlinear or begins at a nonzero value. A line through the points (3, 1) (4, 1.25) (5, 1.5) intersect x = 0 at (0, 0.25). If you want it to be 0 = 0% bonus, then Kecske's 2nd formula would work.
I appreciate all the help and ideas you guys offered.
I ended up creating a table of values to correspond to every number that could be entered in the range that corresponded to a percentage value, used the vlookup formula to search the table and return the proper result from the table
kescke, your formula was good but excel didn’t like it and it lead me down the path of creating a vlookuptable
Sorry, you need to Log In to post a reply to this thread.
