Forum Discussion
IF Then Help
djclements I'd like to add one more layer. For every 4 points negative in difference your quota goes down by 1. For every (4 points) positive, your quota goes up by 2. If under 4 on either side, points remain the same. Depicted below.
| Quota | Gross | Net Difference | Quota Next Week |
| 40 | 30 | -10 | 38 |
| 40 | 50 | 10 | 42 |
| 40 | 43 | 3 | 40 |
JSIMONBOL I think there's a typo in either your explanation or the sample results. Did you mean to say, "For every 4 points positive, your quota goes up by 1"? The sample results seem to indicate you want the same increment of 1 and -1 for every 4 points positive and negative. If so, the TRUNC function is better suited here, because INT doesn't work the same for negative numbers as it does for positive:
=A2+TRUNC(C2/4)Results
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
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