Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Max Values In Columns (2002)

    Dear Helpers,
    Need a formula to do this:
    Look in Col A, get max value. Say this is 8. There can be many 8's.
    Look in Col B, get max value when Col A = 8.
    The attached file shows this need.
    Thank you.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Max Values In Columns (2002)

    If there are more than 1 value in col A that is the max, which value in col B do you want? Do you want the max from col B that corresponds to the 8 or do you want the FIRST one found or something else? The 50 you list is the MAX of 50 and 20 and it is ALSO the first one found so I am not sure which one to pick.

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Max Values In Columns (2002)

    This will give the FIRST value in B that corresponds with the max in A:
    ="The max value in Col A is "&MAX(A1:A10)&" and corresponding Col B max value is "&INDEX(B1:B10,MATCH(MAX(A1:A10),A1:A10,0))

    This will give you the MAX value in B which corresponds to the MAX in A. (It is an ARRAY formula, confirm with ctrl-shift-enter):
    ="The max value in Col A is "&MAX(A1:A10)&" and corresponding Col B max value is "&MAX(IF(A1:A10=MAX(A1:A10),B1:B10))

    Change ranges as appropriate
    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Max Values In Columns (2002)

    The maximum value in A1:A10 is =MAX(A1:A10)

    The maximum value in B1:B10 for cells with the maximum value in A1:A10 is =MAX(IF(A1:A10=MAX(A1:A10),B1:B10,"")) entered as an array formula (confirm with Ctrl+Shift+Enter).

  5. #5
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max Values In Columns (2002)

    Thanks Steve.
    It is the second solution that I am looking for. Great help.
    Selva.

  6. #6
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max Values In Columns (2002)

    Thanks Hans. Problem solved. Great assistance.
    Selva.

Posting Permissions

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