Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post
    I'm not sure how to combine a max formula with an if statement. I have 2 columns, one column has M or F (male or female) in it, the other has Open or Masters in it. I need to find the maximum value in COL C for Males, Females, Males in Open division, Females in Open division, Males in Masters division and Females in masters division. I'm not sure of the best way to construct the formula. Thank you for the help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Let's say the data are in rows 2:11. You can use formulas like this:

    =MAX(IF(($A$2:$A$11="M")*($B$2:$B$11="Open"),$C$2: $C$11))

    This is an array formula, i.e. confirm with Ctrl+Shift+Enter.

    See the attached sample workbook.
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post
    thanks for the formula Hans. Does the "*" between the 2 criteria mean "and", is there an "or" operator?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The * is multiplication, but in this example it acts as "AND". You'd use + (addition) to act as "OR".

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post
    ok, thanks for the explanation.

    Now, I have the max formula working and then i tried to copy it to the next row and change max to MIN. The help screen says to use the fx bar but it doesn't show the word "MAX", it starts with the IF.

    I did click the fx and the braces disappeared and i changed MAX to MIN and then hit ctl+shift+enter but it changed both the formulas to MIN. I have the max formula in B6 and want the min formula in B7.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you select both cells, edit the formula and press Ctrl+Shift+Enter, you'll enter the same array formula in both cells. You can undo this by pressing Ctrl+Z.
    Make sure that only B7 is selected when you edit the formula.

Posting Permissions

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