1. ## 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 )-D2)+(E2-MIN(E:E)))/2,0)  Reply With Quote

2. Can you post some sample data?  Reply With Quote

3. Can you post a workbook and give an example?  Reply With Quote

4. Attached please find my Games Behind workbook  Reply With Quote

5. 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)  Reply With Quote

6. In the K2 enter the following Array formula

[vba]=(MAX(IF(A:A=A2,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  Reply With Quote

7. Thank you for all your assistance. worked like a charm  Reply With Quote

8. Which one worked like a charm?  Reply With Quote

9. =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)  Reply With Quote

10. 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
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.  Reply With Quote

11. mistake  Reply With Quote

12. 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.  Reply With Quote

13. Mistake  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•