Difference between positive negative (any variation)

[roondog248]

		Score	Yards/Point	Yards / Play
		Offense	Defense	+/-	Offense	Defense	+/-	Offense	Defense	+/-
Chicago Bears	23.0	25.0	-2.0	15.2	14.9	0.3	5.3	6.2	-0.9
	18	21	21	15	18	18	21	28	31
Carolina Panthers	18.3	24.0	-5.8	18.0	15.6	2.4	5.3	6.0	-0.6
		28	17	27	27	14	21	23	23	25
			----->	3.8		----->​	2.1			0.2

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

-2	-3		-1	1	-1	-5
2	3		1	-1	-1	1
2	3		1	-1	-1	1
2	-3		-5	5	-5	-1

<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

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,

-4.30	9.80		14.1
-6.40	-6.90		0.5
0.07	-0.01		0.08
0.30	2.10		1.8

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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()