Consulting

Results 1 to 13 of 13

Thread: Games Behind Formula

  1. #1
    VBAX Regular
    Joined
    May 2009
    Posts
    76
    Location

    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)

    I appreciate any help you can give.

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Can you post some sample data?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Can you post a workbook and give an example?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Regular
    Joined
    May 2009
    Posts
    76
    Location
    Attached please find my Games Behind workbook

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    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

  7. #7
    VBAX Regular
    Joined
    May 2009
    Posts
    76
    Location
    Thank you for all your assistance. worked like a charm

  8. #8
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    Which one worked like a charm?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    VBAX Regular
    Joined
    May 2009
    Posts
    76
    Location
    =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)

  10. #10
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    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.

  11. #11
    mistake
    Last edited by HollyM; 10-15-2021 at 11:13 AM.

  12. #12
    Banned VBAX Newbie
    Joined
    Oct 2021
    Posts
    3
    Location
    Quote Originally Posted by Loss1003 View Post
    =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.
    Last edited by AndySm; 10-15-2021 at 12:02 PM. Reason: mistake

  13. #13
    Banned VBAX Newbie
    Joined
    Oct 2021
    Posts
    3
    Location
    Mistake

Posting Permissions

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