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.

 

[Peter_SSs]

As you can see, the number (in red) in the H column is incorrect.

1. I can only see that for certain if you clearly point out what the correct result should be and how you calculate that correct result.

2. Is the "0.2" at the bottom right of your table correct? If so, how was it calculated? If not, what should it be, and why?

 

[roondog248]

Score

Yards/Point

Yards / Play

Net Yards/Pass

Offense

Defense

+/-

Offense

Defense

+/-

Offense

Defense

+/-

Offense

Defense

+/-

New York Jets

-4.3

-6.4

0.7

0.3

San Diego Chargers

9.8

-6.9

-0.1

2.1

----->

14.0

----->​

0.5

----->​

0.8

----->​

1.8

<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"><col width="75"><col width="75"><col width="75"></colgroup><tbody>
</tbody>

in order to make this less confusing refer to this one, b/c it has only the values in question.

All the values with the arrow next to them are correct. I worked them out on a calculator.
1st column of number is - - +
2nd is - - -
3rd is + - -
4th is + - +

all these numbers will change every time i enter a new team. So the only thing i can think of is 3 or 4 nested If formulas that apply all the rules of subtracting positives and negatives.

 

[Aladin Akyurek]

Looks like:

=ABS(E3-E5)

where E3 is -4.3 and E5 is 9.8.

This seems to hold for all pair of scores.

 

[roondog248]

Looks like:

=ABS(E3-E5)

where E3 is -4.3 and E5 is 9.8.

This seems to hold for all pair of scores.

It works for these numbers but when I changed the team (browns)
it's -1.9
8.9
and ABS(E3-e5) is +10.8

when it should be -10.8

 

[Aladin Akyurek]

It works for these numbers but when I changed the team (browns)
it's -1.9
8.9
and ABS(E3-e5) is +10.8

when it should be -10.8

Try to word the rules that govern the calculation, otherwise you would get a guess covering partial examples.

 

[roondog248]

After many many hours of aggravation I think I have just figured it out.
As I attempted to articulate the comment that would respond to yours, I had to think about all the rules of subtracting positive and negative numbers and any variation (+ - +) (- - +) (+ - -) + - +) and then transfer those rules to Excel talk.

I think I have figured it out....

=IF(AND(E17>0,E19>0),MIN(E17,E19)-MAX(E17,E19),IF(AND(E17<0,E19<0),(ABS(E17-E19)),IF(AND(E17>0,E19<0),MAX(E17,E19)-(MIN(E17,E19)),MIN(E17,E19)-(MAX(E17,E19)))))

I'm sure I will find a few flaws in this formula as the values change with the teams.
Hopefully I will be able to adjust.

If anyone sees a flaw, or has an easier way, let me know.

thanks

 

[Aladin Akyurek]

I'd still ask for the rules. Try to make an exhaustive set of examples along with the manually calculated desired results.

 

[Peter_SSs]

I think I have figured it out....

=IF(AND(E17>0,E19>0),MIN(E17,E19)-MAX(E17,E19),IF(AND(E17<0,E19<0),(ABS(E17-E19)),IF(AND(E17>0,E19<0),MAX(E17,E19)-(MIN(E17,E19)),MIN(E17,E19)-(MAX(E17,E19)))))

If anyone sees a flaw ...

Yes, I see a flaw, though I don't know if it's a flaw in your formula or a flaw in the red statement below.
The flaw is that the formula above and the red statement below cannot both be correct because the formula does not produce the values you state are correct, as evidenced by my screen shot below.
Hence the need for an accurate response to Aladin's request.

Score

Yards/Point

Yards / Play

Net Yards/Pass

Offense

Defense

+/-

Offense

Defense

+/-

Offense

Defense

+/-

Offense

Defense

+/-

New York Jets

-4.3

-6.4

0.7

0.3

San Diego Chargers

9.8

-6.9

-0.1

2.1

----->

14.0

----->​

0.5

----->​

0.8

----->​

1.8

<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"><col width="75"><col width="75"><col width="75"></colgroup>

All the values with the arrow next to them are correct. I worked them out on a calculator.
1st column of number is - - +
2nd is - - -
3rd is + - -
4th is + - +

all these numbers will change every time i enter a new team. So the only thing i can think of is 3 or 4 nested If formulas that apply all the rules of subtracting positives and negatives.

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N
17			-4.3			-6.4			0.7			0.3
18
19			9.8			-6.9			-0.1			2.1
20
21	Stated as correct	----->	14		----->	0.5		----->	0.8		----->	1.8
22
23	Post #7 formula:		-14.1			0.5			0.8			-1.8
roondog248

 

[roondog248]

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.