Forum Discussion
JSIMONBOL
Mar 07, 2024Copper Contributor
IF Then Help
I want to create a score system where if I INPUT 2 then the adjustment is -1, if its 4 then its -2, if its 6, then it's -3 and so forth. Same goes for odd number where 3 is -1, 5 is -2, and 7 is -3. ...
- Mar 07, 2024
JSIMONBOL
Mar 07, 2024Copper Contributor
djclements not a typo. If you are above quota your quota next week goes up 2 points for every 4 points over quota. If you are below quota your quota then it goes down 1 point for every 4 points under. So your rule is almost there I would just need the calculation for D2 to be 39 and not 38. So it would have to conditionally know to deduct 1 or add 2 depending on the difference for every 4 points over or under
djclements
Mar 07, 2024Bronze Contributor
JSIMONBOL Yeah, I still don't think you've got the results right... based on the logic you've stated, D2 would still be 38, and D3 would be 44.
=A2+IF(C2<0, TRUNC(C2/4), TRUNC(C2/4)*2)
Results
- JSIMONBOLMar 07, 2024Copper Contributor
New Logic below. I see where i messed up. Basically because you went over your quota you get rewarded with more hard work with a higher quota that following week. If you are below quota, you only get rewardd by your quota going down by 1 point. Every 4 positive points is worth 2 (so 40 this week to 42 next week) and every 4 negative points is worth -1 (so 40 this week to 39 next week.) I APPRECAITE YOU
Quota Gross Net Difference Next Weeks quota 40 48 8 42 40 36 -4 39 - djclementsMar 07, 2024Bronze Contributor
JSIMONBOL
Sorry, but I'm going to stop now, because we're just going in circles. The logic in your explanation is exactly the same as before, but your sample results are back to the original problem of depicting a reward value of 1 for every 4 positive points. Since we're now dealing with numbers that are evenly divisible by 4 in this example, the basic formula to calculate the quota change is:
Net Difference / 4 * Worth
In your first example the Quota is 40 and they Grossed 48, for a Net Difference of 8. Since it's a positive number, the Worth is 2. Therefore:
8 / 4 * 2 = 4
Add that to 40 and you get 44 (not 42).
Either the first formula I suggested is correct, or the second one is correct. Based on your explanation, the second one is correct. Based on the sample results you shared, the first one is correct. I'll let you decide.
- JSIMONBOLMar 07, 2024Copper ContributorI’m sorry. I did have a typo. Below is exactly how I would like it. Over 4 plus two points. Every Under four, minus 1.
Quota Gross Net Difference Next Weeks quota
40 44 4 42
40 36 -4 39
Quota Gross Net Difference Next Weeks quota
40 48 8 44
40 36 -8 38