IF Then Help

[JSIMONBOL]

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.  

So in the cell below, if i type in the number 2 it should show in the adj column as a -1. 

RESULT

Open	O/H
Holes	Adj.
2	-1

REFERENCE

Number	Deduction
1	0
2	-1
3	-1
4	-2
5	-2
6	-3
7	-3
8	-4
9	-4
10	-5

[#2 · Accepted solution]

@JSIMONBOL No need for IF here... the INT function should do the trick:

=-INT(A3/2)

Results

[#3]

You sir are the man times infinity plus 1

[#4]

@JSIMONBOL 

=-INT(A1/2)

[#5]

@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

[#6]

@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

[#7]

@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

[#8]

@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

[#9]

@djclements 

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

[#10]

@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.

[#11]

I’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

[#12]

@JSIMONBOL Please see the attached workbook... input the quota and gross amounts in columns A and B. Columns C and D contain the formulas.

[#13]

YESSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS!