Newbie Question - double negatives/double positives

  • Thread starter Thread starter HDTV-slingr
  • Start date Start date
H

HDTV-slingr

Ok, I'm tearing my hair out. I've searched for about 6 hours (and I
thought I was pretty good at searching/researching on the 'net before
this) and apparently, I'm just blonde and that's that.

It seems to me what I'm trying to do would be in a "Welcome to Excel
101" tutorial on the first page... argh!

Anyway, I'm trying to get a final sum of two cells. Below, I'll try
to give a step by step example of the possible scenarios I'm trying to
resolve in one single cell, the sum of two other cells, which may
contain double positives, double negatives, or a positive and a
negative, to arrive at the difference between the two numbers.


For instance, let's say both cells contain the average win or loss
margin of two football teams and both teams typically lose to the
opposition, but one team usually loses by a greater margin than the
other team. If Team "A" usually loses by 20 points and Team "B"
usually only loses by 3 points, we can easily get Excel to display a
positve win for team B by 17 points.

Conversely, let's say Team "A" usually wins by 20 points and Team "B"
usually only wins by 3 points. We can easily get Excel to calculate a
17 point win for Team "A".

Now let's say, Team "A" usually wins by 20 points but Team "B" usually
loses by 3 points, we can easily get Excel to calculate a 23 point win
for Team "A".

So.....

We've got 3 cells - A3 equals the average points margin (plus or
minus) of win or loss by one team and A4 equals the average points
margin (plus or minus) of win or loss by the other team. A5 is
suppossed to calculate the prediction of victory margin by one of the
two teams. What's the easiest way to do this in Excel, please?

Thank you in advance and please excuse my newbieness if this
question's been answered a thousand times before :-)
 
Supposing that team A is in column A and team B in column B, use =-A1+B1 in
column C to get the result between those teams from the perspective of the B
team... I have tested it and seems to work with any possible combination I
came up with...:)

Hope this helps...
 
If you want to predict the margin of A's victory(+) or loss(-), use

=A1-B1

A B C
1 -20 -3 -17
2 20 3 17
3 10 -10 20


If you want to predict the margin of B's victory or loss use

=B1-A1
A B C
1 -20 -3 17
2 20 3 -17
3 10 -10 -20

Another way:

=IF(A1>B1,"A wins by ","B wins by ") & ABS(A1-B1)

A B C
1 -20 -3 B wins by 17
2 20 3 A wins by 17
3 10 -10 A wins by 20
 
Ok, I'm tearing my hair out. I've searched for about 6 hours (and I
thought I was pretty good at searching/researching on the 'net before
this) and apparently, I'm just blonde and that's that.

It seems to me what I'm trying to do would be in a "Welcome to Excel
101" tutorial on the first page... argh!

Anyway, I'm trying to get a final sum of two cells. Below, I'll try
to give a step by step example of the possible scenarios I'm trying to
resolve in one single cell, the sum of two other cells, which may
contain double positives, double negatives, or a positive and a
negative, to arrive at the difference between the two numbers.


For instance, let's say both cells contain the average win or loss
margin of two football teams and both teams typically lose to the
opposition, but one team usually loses by a greater margin than the
other team. If Team "A" usually loses by 20 points and Team "B"
usually only loses by 3 points, we can easily get Excel to display a
positve win for team B by 17 points.

Conversely, let's say Team "A" usually wins by 20 points and Team "B"
usually only wins by 3 points. We can easily get Excel to calculate a
17 point win for Team "A".

Now let's say, Team "A" usually wins by 20 points but Team "B" usually
loses by 3 points, we can easily get Excel to calculate a 23 point win
for Team "A".

So.....

We've got 3 cells - A3 equals the average points margin (plus or
minus) of win or loss by one team and A4 equals the average points
margin (plus or minus) of win or loss by the other team. A5 is
suppossed to calculate the prediction of victory margin by one of the
two teams. What's the easiest way to do this in Excel, please?
Click to expand...

OK, thanks for trying to help. I've figured it out (finally). The
result I was seeking seems to work in all instances (i.e. double
negative, double positive, negative & positive) when I use this
formula (the cells being added are I4 and I5):

=SUM(LARGE(I4:I5,1)-SMALL(I4:I5,1))

This is what I was after.

I'm sure there's an easier way to do this but the simple =I4-I5 or
=I4+I5 returns false results in the case of double negative numbers
being added (i.e., -5 and -4 return a result of -9 or 9 for example,
instead of the positive number 1, which is what I was looking for).
The above formula appears to work in all scenarios.

Thanks again, y'all :-)
 
If you want to predict the margin of A's victory(+) or loss(-), use

=A1-B1

A B C
1 -20 -3 -17
2 20 3 17
3 10 -10 20


If you want to predict the margin of B's victory or loss use

=B1-A1
A B C
1 -20 -3 17
2 20 3 -17
3 10 -10 -20

Another way:

=IF(A1>B1,"A wins by ","B wins by ") & ABS(A1-B1)

A B C
1 -20 -3 B wins by 17
2 20 3 A wins by 17
3 10 -10 A wins by 20
Click to expand...

Okay, maybe I'm pushing my luck but how about this scenario:

B4 is text - "Team A"
B5 is text - "Team B"

I4 is an active numeric cell calculating average points per game
(could be positive or negative number) for "Team A"

I4 is an active numeric cell calculating average points per game
(could be positive or negative number) for "Team A"

J5 is an active numeric cell, calculating the predicted victory margin
in the game, however; it just returns the number but does not display
any text to say WHICH team ("Team A" or "Team B") should win. This is
the current formula in J5 (thanks man!):

"=ABS(I4-I5)"

My question is, can this cell be modified to display not only the
victory margin but *also* the text describing which of the two teams
should win the game in cell J5?

For an example, IF:

"Team A" generally loses, averaging -4.27 points per game.

"Team B" generally loses, averaging -2.91 points per game.

Desired result in cell J5 would be "Team A wins by 1.4"

Another example, IF:

"Team A" generally wins, averaging +2.36 points per game (without the
"+" sign displayed).

"Team B" generally loses, averaging -2.73 points per game.

Desired result in cell J5 would be "Team A wins by 5.1"

Is this possible with Excel? I wouldn't be surprised at all.

Thanks in advance and thanks again :-)
 
One way:

J5: =IF(I4>I5, B4, B5) & " wins by " & ABS(I4-I5)

and to avoid playing 'bring me a rock', you might want to consider
something like:

J5: =IF(I4=I5,"Tie",IF(I4>I5,B4,B5) & " wins by " & ABS(I4-I5))

or even

J5: =IF(COUNT(I4:I5)<2,"?",IF(I4=I5,"Tie", IF(I4>I5,B4,B5) & " wins
by " & ABS(I4-I5)))
 
J5: =IF(I4=I5,"Tie",IF(I4>I5,B4,B5) & " wins by " & ABS(I4-I5))
Click to expand...

Thank you! Just BEAUTIFUL... works like a charm, my friend. God
Bless ya and Happy Holidays :-)
 
One way:

J5: =IF(I4>I5, B4, B5) & " wins by " & ABS(I4-I5)

and to avoid playing 'bring me a rock', you might want to consider
something like:

J5: =IF(I4=I5,"Tie",IF(I4>I5,B4,B5) & " wins by " & ABS(I4-I5))

or even

J5: =IF(COUNT(I4:I5)<2,"?",IF(I4=I5,"Tie", IF(I4>I5,B4,B5) & " wins
by " & ABS(I4-I5)))
Click to expand...

Okay, one more question with this formula -

Is it possible to get the formula to return a number rounded to one
decimal point, i.e., "Team A wins by 5.5" as opposed to "Team A wins
by 5.4333333333333"? I can't seem to find a way to modify this
formula to work that way. Thanx again and thanks in advance.
 
Back
Top