Excel formula, IF and Rank?

Topic

New Reply

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.

Reply | Quote

Replies

1) Select D1:D12
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

Reply | Quote

If you want the items from A (and there were no Duplicate values) you can use the formula (confirm with ctrl-shift-enter) in D1:D12:

=IF(LARGE((B1:B25>=150)*B1:B25,ROW())=0,””,INDEX(A1:A25,MATCH(LARGE((B1:B25>=150)*B1:B25,ROW()),B1:B25,0)))

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

=IF(LARGE((B1:B25>=100)*(B1:B25=100)*(B1:B25<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

Reply | Quote

Thanks for the formulas. Is there a way to deal with duplicates using formulas? Thanks

Reply | Quote

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.

Reply | Quote