Excel 2003: Need Rankif Formula

adamchicago
adamchicago used Ask the Experts™
on
Can someone help figure out a Rankif formula?

Need to rank a column of numbers (column B) based on if another column has an "X" in it (column C)

Thank you!!!!!!!!!!!!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jorge PaulinoIT Pro/Developer
Top Expert 2008

Commented:
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Of you add a helper column ... for example D and then in there use:

=IF(C1="x",B1,"") ... drag down as required

Then base your rank function on the helper column.

Chris

Author

Commented:
I tried the link ideas but they don't work for 2003...but they're close to the solution.

Any other ideas?
Most Valuable Expert 2013
Commented:
Hello adam

Assuming data from row 2 to row 10 try this formula in D2 copied down

=IF(C2="x",SUMPRODUCT((C$2:C$10="x")*(B$2:B$10>B2))+1,"")

That will return a blank if column C isn't "X", otherwise it ranks with highest number ranked 1....change > to < for lowest number ranked 1

see attached example - press F9 to re-generate random numbers in column B

regards, barry
27037519.xls

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial