# Thread: Excel formula, IF and Rank?

1. 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.

2. 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. 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. Thanks for the formulas. Is there a way to deal with duplicates using formulas? Thanks

5. 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
•