Difference between positive negative (any variation)

[roondog248]

				----->​

<colgroup><col style="width: 75px"><col width="75"><col width="75"><col width="75"><col width="75"><col width="75"><col width="75"><col width="75"><col width="75"><col width="75"><col width="75"></colgroup><tbody>
[TD="colspan: 3, align: center"]Score[/TD]
[TD="colspan: 3, align: center"]Yards/Point[/TD]
[TD="colspan: 3, align: center"]Yards / Play[/TD]

[TD="align: center"]Offense[/TD]
[TD="align: center"]Defense[/TD]
[TD="align: center"]+/-[/TD]
[TD="align: center"]Offense[/TD]
[TD="align: center"]Defense[/TD]
[TD="align: center"]+/-[/TD]
[TD="align: center"]Offense[/TD]
[TD="align: center"]Defense[/TD]
[TD="align: center"]+/-[/TD]

[TD="colspan: 2, align: center"]Chicago Bears[/TD]
[TD="align: center"]23.0[/TD]
[TD="align: center"]25.0[/TD]
[TD="align: center"]-2.0[/TD]
[TD="align: center"]15.2[/TD]
[TD="align: center"]14.9[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"]5.3[/TD]
[TD="align: center"]6.2[/TD]
[TD="align: center"]-0.9[/TD]

[TD="colspan: 2"][/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"] 28 [/TD]
[TD="align: center"] 31 [/TD]

[TD="colspan: 2, align: center"]Carolina Panthers[/TD]
[TD="align: center"]18.3[/TD]
[TD="align: center"]24.0[/TD]
[TD="align: center"]-5.8[/TD]
[TD="align: center"]18.0[/TD]
[TD="align: center"]15.6[/TD]
[TD="align: center"]2.4[/TD]
[TD="align: center"]5.3[/TD]
[TD="align: center"]6.0[/TD]
[TD="align: center"]-0.6[/TD]

[TD="align: center"] 28 [/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]25[/TD]

[TD="align: right"]----->[/TD]
[TD="align: center"]3.8[/TD]
[TD="align: center"] 2.1 [/TD]

[TD="align: center"]0.2[/TD]

</tbody>

All the metrics under Offense & Defense are the result of a vlookup formula, so they change everytime depending on the team.
In the +/- column, (difference between the differences) both of the numbers will sometimes be positive, sometimes negative, sometimes positive & negative.

As you can see, the number (in red) in the H column is incorrect.
I need help figuring out a formula that will retain all the rules for subtracting positive, negatives (every variation)
Any help would be greatly appreciated.

 

[Aladin Akyurek]

you're right, that formula is inaccurate.
I'm not sure what you want me to explain.
i'm looking for the formula to find the difference between the two numbers, whether that is positives - negatives, negatives - negatives, positives - positives.
With all due respect, I think those rules are common knowledge.

I will have to adjust those flaws late on today after work.

-2; -3 ===> x

-2; 3 ===> y

2; -3 ===> z

2; 3 ===> w

Would you please fill in the common knowledge that is missing: What values do x, y, z, and w should take?

 

[West Man]

It seems that there are only 4 possibilities to find a "difference" that do not result in all positive or all negative numbers. In the example below the two numbers from which the "difference" ar in columns A & B.

[TABLE="width: 324"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD="align: right"]-2[/TD]
[TD="align: right"]-3[/TD]
[TD] [/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]-3[/TD]
[TD] [/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-1[/TD]
[/TR]
</tbody>[/TABLE]

The 4 formulas are: =B1-A1
=A1-B1
=MIN(A1:B1)-(MAX(A1:B1))
=MAX(A1:B1)-ABS(MIN(A1:B1))

Perhaps one of the latter 2 meets your needs

 

[West Man]

using your example in post #3,

[TABLE="width: 266"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]-4.30[/TD]
[TD="align: right"]9.80[/TD]
[TD] [/TD]
[TD="align: right"]14.1[/TD]
[/TR]
[TR]
[TD="align: right"]-6.40[/TD]
[TD="align: right"]-6.90[/TD]
[TD] [/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD="align: right"]0.07[/TD]
[TD="align: right"]-0.01[/TD]
[TD] [/TD]
[TD="align: right"]0.08[/TD]
[/TR]
[TR]
[TD="align: right"]0.30[/TD]
[TD="align: right"]2.10[/TD]
[TD] [/TD]
[TD="align: right"]1.8[/TD]
[/TR]
</tbody>[/TABLE]

formula seems to be: =IF(B6>A6,B6-A6,A6-B6)

 

[roondog248]

Yeah, it turns out that I was making this way more complicated that it actually needed to be.
I was getting so confused because some of the stats are 'higher the better', some 'lower the better'
E.g. Yards Per Point = lower the better.
Yards Per Play = higher the better.

The google docs sheet knows what to do when a negative is subtracted from a positive, negative minus a negative...and so on.


So, Higher The Better is =Max() - MIN()
Lower The Better is =Min() - Max()