+ Reply to Thread
Results 1 to 9 of 9

Identify max value for multiple variables?

  1. #1
    Registered User
    Join Date
    04-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Identify max value for multiple variables?

    Match1 Match2 Match3 Match4 Match5 Match6 Total
    Home
    Away
    ABS difference
    Winning team is H or A

    I have 2 teams (Home & Away) playing 6 matches of multiple games. At the end of the match each team enters their score.

    I can calculate:
    1) The total for each team.
    2) The difference between each team in each individual match using, =ABS(B2-B3)
    3) In each match which team won using, =IF(B3>B2,A3,A2), Where teams (home& away) are at A2 & A3.

    I would like to calculate which match has the highest difference for the Home team and same for the away team?

    Can anybody help.
    Attached Files Attached Files
    Last edited by AndyH50; 04-01-2013 at 12:04 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Identify max value for multiple variables?

    Hang on a second - I can't find my Crystal ball

    ..... seriously though.. Can you upload the file please - it makes it much easier !!
    Last edited by AndyLitch; 04-01-2013 at 12:04 PM.
    Elegant Simplicity............. Not Always

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Identify max value for multiple variables?

    Hi and welcome to the forum

    While Andy (Litch) finds his crystal ball, maybe you could add more detail to your question?

    It would help tremendously if you could provide some sample data (workbook), showing what data you are working with, what your expected outcome would be, and how you arrived at that.
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Identify max value for multiple variables?

    1. Put =B2-B3 in B10 and paste it across to G10
    2. =MAX(B10:G10) is then the highest Home team Goal diff win
    3. =Abs(Min(B10:G10) ) is the highest Away team win

  5. #5
    Registered User
    Join Date
    04-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Identify max value for multiple variables?

    Thanks for the welcome, I'm a newbie but managed to add my attachment. Many thanks.

    Quote Originally Posted by FDibbins View Post
    Hi and welcome to the forum

    While Andy (Litch) finds his crystal ball, maybe you could add more detail to your question?

    It would help tremendously if you could provide some sample data (workbook), showing what data you are working with, what your expected outcome would be, and how you arrived at that.
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  6. #6
    Registered User
    Join Date
    04-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Identify max value for multiple variables?

    , Well done Andy, many thanks.

    Can you help to translate that winning Score into a match No. ie. Rink No?

    Eternally grateful.

    Quote Originally Posted by AndyLitch View Post
    1. Put =B2-B3 in B10 and paste it across to G10
    2. =MAX(B10:G10) is then the highest Home team Goal diff win
    3. =Abs(Min(B10:G10) ) is the highest Away team win

  7. #7
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Identify max value for multiple variables?

    Bit awkward with the layout as it is but if conditional formatting is good enough then

    1. In Cell B4 conditional format put =B4 = MAX($B$4:G$4) and format it
    then copy/paste the format across B4:G4

  8. #8
    Registered User
    Join Date
    04-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Identify max value for multiple variables?

    Apologies, but I need clarification as I am not getting this right.
    Thanks

    Quote Originally Posted by AndyLitch View Post
    Bit awkward with the layout as it is but if conditional formatting is good enough then

    1. In Cell B4 conditional format put =B4 = MAX($B$4:G$4) and format it
    then copy/paste the format across B4:G4

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Identify max value for multiple variables?

    1. highlight the range you want to apply the conditional formatting to
    2. on the home tab, styles, select CF
    3. select new rule, select use formula
    4. enter the suggested formula

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1