Rank a cell value thats not in a list

[batchjb69]

Is it possible to rank a cell value that's not in the list. Please see attached example

rank.xlsx

[shg]

What should the answer be for the example?

[mrice]

Try this user defined function

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a G2, enter =RankSpecial(F2,B1:B27)

Remember to save the workbook as a macro enabled workbook .xlsm

[TMS]

If you sort on column B, smallest to largest, and add a zero (0), in A1 and B1, you can try:

Please Login or Register  to view this content.

Regards, TMS

[Tony Valko]

Try this...

=RANK(F2,(F2,B1:B27))