Results 1 to 5 of 5
  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 do the formulas attached. I need to capture one set of values over 150 in COL D and another set of values between 100 and 150 in column E. I also need to rank them. Is there a way to do it in one formula? Thank you for the help.
    Attached Files Attached Files

  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
    1) Select D112
    In the formula bar enter the formula:

    =LARGE((B1:B25>=150)*B1:B25,ROW())

    Confirm with ctrl-shift-enter

    2) Select E1:E12
    In the formula bar enter the formula:

    =LARGE((B1:B25>=100)*(B1:B25<150)*B1:B25,ROW())

    Confirm with ctrl-shift-enter

    3) to hide the "zeroes" and format to 1 Dec place:
    select D1:E12
    Format - cells
    Category: Custom
    type: 0;;
    [ok]

    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
    If you want the items from A (and there were no Duplicate values) you can use the formula (confirm with ctrl-shift-enter) in D112:

    =IF(LARGE((B1:B25>=150)*B1:B25,ROW())=0,"",INDEX(A 1:A25,MATCH(LARGE((B1:B25>=150)*B1:B25,ROW()),B1:B 25,0)))

    And in E1:E12 (confirm with ctrl-shift-enter)

    =IF(LARGE((B1:B25>=100)*(B1:B25<150)*B1:B25,ROW()) =0,"",INDEX(A1:A25,MATCH(LARGE((B1:B25>=100)*(B1:B 25<150)*B1:B25,ROW()),B1:B25,0)))

    But this fails since B1 and B3 have identical values so the MATCH gets the first one in both entries

    Steve

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks for the formulas. Is there a way to deal with duplicates using formulas? Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You might try to adapt Aladin Akyurek's system described in Help to fully understand this formula - MrExcel Message Board.

    But I think it would be a lot easier to use Advanced Filter to copy the data with values over 150 (or between 100 and 150) in column B to a separate range, and to sort the copied range.

Posting Permissions

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