Compare Best WIN and worst LOSS in Columns

[Duane]

Hey folks! Now you have me learning more and more about Excel, I am trying
to do things I never thought about before!

I have a list of game scores, col 'E' TEAM PLAYED Against, col 'F' for
opponents and col'G' for Home Team. Col 'H' is either WIN or LOSS based on
the scores in two previous columns.
What I am trying to do is: of the WINs in col'H' , which game did we do
best in (IE: difference in col'G' and col'F' was greatest, and display TEAM
name)
Of "LOSS" in col'H' which did we do poorest in (IE: difference in col'G' and
col'F' was again the greatest)

I have tried a few different scenarios but can't seem to find one that works.

Thanks again!
Duane

 

[T. Valko]

Try these array formulas**.

E2:E11 = team played
F2:F11 = their score
G2:G11 = your score
H2:H11 = W or L

For the team you did the best against (largest difference in score):

=INDEX(E2:E11,MATCH(MAX(IF(H2:H11="W",G2:G11-F2:F11)),IF(H2:H11="W",G2:G11-F2:F11),0))

For the team that really cleaned your clock <g>:

=INDEX(E2:E11,MATCH(MIN(IF(H2:H11="L",G2:G11-F2:F11)),IF(H2:H11="L",G2:G11-F2:F11),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Duane]

You folks always find simpler ways to get a task accomplished!
I cheated a little by taking a hidden column 'L' and doing '=SUM(G5-F5)'
down for all scores so I had POSitive for wins, and negatives for loss.
Then, where I wanted to display the Team we creamed, I did
=INDEX(D5:L54,MATCH((INDEX(L5:L54,MATCH(MAX(L5:L54),L5:L54,FALSE),1)),L5:L54,FALSE),1)

With my formula, I just change the last #1 (TEAM NAME COLUMN) to (7) and I
see the date the game was played!

For WORST, I change MAX to MIN and see who creamed us.....

Thanks again for everything you guys do here... really a major time saver
with Excel!

Tks
Duane

 

[T. Valko]

I cheated a little by taking a hidden column 'L'

and doing '=SUM(G5-F5)'
=INDEX(D5:L54,MATCH((INDEX(L5:L54,MATCH(MAX(L5:L54),L5:L54,FALSE),1)),L5:L54,FALSE),1)

If you're using an intermediate cell to calculate the score difference then
you can reduce that formula to:

=INDEX(D554,MATCH(MAX(L5:L54),L5:L54,0))