Games Behind Formula

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.

Can you post some sample data?

Can you post a workbook and give an example?

Attached please find my Games Behind workbook

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)

In the K2 enter the following Array formula

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

Thank you for all your assistance. worked like a charm

Which one worked like a charm?

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

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

Quote:

---

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

mistake

Quote:

---

Originally Posted by Loss1003

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

Mistake