PDA

View Full Version : [SOLVED] Games Behind Formula



Loss1003
02-04-2010, 12:16 PM
Need some help adjusting the following games behind formula to re-calculate after the year changes based on the data located in my table.

The data is sorted by the year (column A), team name (column B), Wins (Column C), Losses (Column D), and Games Behind in Column (K).

I’m having difficulties when the year (Column A) changes.
The formula is set up to calculate based of the max for all years. I need to adjust the formula to base off each individual year. Therefore, I need to adjust the Max/Min for each year to start over when the year changes.

=IF(AND(A2=A1),((MAX(D :D)-D2)+(E2-MIN(E:E)))/2,0)

I appreciate any help you can give.

mdmackillop
02-04-2010, 12:21 PM
Can you post some sample data?

Bob Phillips
02-04-2010, 12:21 PM
Can you post a workbook and give an example?

Loss1003
02-04-2010, 12:53 PM
Attached please find my Games Behind workbook

Bob Phillips
02-04-2010, 02:17 PM
You don't make it easy, so I am going to guess.

Try this array formula

=IF($A2<>$A1,0,((MAX(IF($A$2:$A$24=$A2,$D$2:$D$24))-$D2)+($E2-MIN(IF($A$2:$A$24=$A2,$E$2:$E$24))))/2)

mbarron
02-04-2010, 02:20 PM
In the K2 enter the following Array formula

=(MAX(IF(A:A=A2,D:D,0)-D2)+E2-MIN(IF(A:A=A2,E:E)))/2 Confirm with Ctrl+Shift+Enter
Copy down the rest of the column

Loss1003
02-04-2010, 02:21 PM
Thank you for all your assistance. worked like a charm

Aussiebear
02-04-2010, 10:52 PM
Which one worked like a charm?

Loss1003
02-05-2010, 07:34 AM
=IF($A2<>$A1,0,((MAX(IF($A$2:$A$111=$A2,$D$2:$D$111))-$D2)+($E2-MIN(IF($A$2:$A$111=$A2,$E$2:$E$111))))/2)

mbarron
02-05-2010, 07:50 AM
The formulas that xld and I supplied are identical except he is doing a check on the year change, which is not really necessary. You can change the formula to
((MAX(IF($A$2:$A$111=$A2,$D$2:$D$111))-$D2)+($E2-MIN(IF($A$2:$A$111=$A2,$E$2:$E$111))))/2 and receive the same results.
The advantage to this formula would be that if you were to sort by the team, you can see how they finished each year. With the year check, the games behind will be 0 for all teams.

HollyM
10-15-2021, 10:14 AM
mistake

AndySm
10-15-2021, 11:59 AM
=IF($A2<>$A1,0,((MAX(IF($A$2:$A$111=$A2,$D$2:$D$111))-$D2)+($E2-MIN(IF($A$2:$A$111=$A2,$E$2:$E$111))))/2)
This really works, thanks a lot guys! I was searching for it.

AndySm
10-15-2021, 12:05 PM
Mistake