RANKIF function which can take formulas giving dynamic ranges as arguments

[Marbleking]

Hi,

I am trying to write a "RANKIF" formula which can take as arguments formulas that give dynamic ranges. Please see attached workbook.

Considerations:

1. A descending "RANKIF" of column B with respect to column A is needed.
2. Numbers (in text format) to be ranked consists of 18 digits, but Excel only keeps the 15 first digits, while turning the rest into zeroes except when the string is kept as text format.
3. There is no need for all the digits for the proposed use, so a workaround is to create a column which extracts only the 15 last digits and turns them into numbers.
4. Functions like COUNTIFS, RANK and SUMPRODUCT cannot take calculation of dynamic ranges as arguments, e.g.: NUMBERVALUE(RIGHT(B2:B33;15))
5. Therefore, a formula to calculate the rank of these numbers must be entered in yet another separate column.

Need:

Formula to calculate rank directly without helper column and with a dynamic array as output. (The LET function is unfortunately not available in my version of Excel yet).

Currently I am using the following formula:

Please Login or Register  to view this content.

Best regards,
Marbleking

[Bo_Ry]

Please try

=COUNTIFS(A2:A33,A2:A33,B2:B33,"<"&UNICHAR(888)&B2:B33)+1

[Phuocam]

Delete post.

[Marbleking]

Thank you, both! Great tips & tricks!