Identify Highest value in a Range and Return a desired Number ( UDF/Formula) would be highly appreciated

[Please_H]

Dear All,

I hope everyone is safe.

I am in a need of having to look at a 'range of Number Values' and get a desired number ranging from 1 to 200 as the range would keep updating up to 200 entries.

Ex :

Data

A1 300
A2 400
A3 500
A4 600
A5 700
A6 1500
A7 200
A8 100
A9 -
A10 -

Outcome Needed

B1 - It looks from A1 to A200 and finds the highest number and returns 200, then returns 199 to the number next to the highest in the Range and goes as far as to return 1 to the lowest number and returns a "0" for the "-" ( A9, A10 are "-" because the Colomn A returns "-" until it picks a value from a different sheet. If same values are found for an example in cell A23 and A56 it gives the higher number to the A23 and a lower number to A56 example : B23 > 79, B56> 78

Thanks a lot.

Stay safe.

 

[steve the fish]

See if this does what you need:

=IFERROR(ROWS($A$1:$A$200)-RANK(A1,$A$1:$A$200)-COUNTIF($A$1:A1,A1)+2,0)

 

[Please_H]

See if this does what you need:

=IFERROR(ROWS($A$1:$A$200)-RANK(A1,$A$1:$A$200)-COUNTIF($A$1:A1,A1)+2,0)

OMGGG !!!

You've nailed it bro....

I didn't understand this part...
Can you be kind enough to explain what happens here?

COUNTIF($A$1:A1,A1)+2,0)

Thanks a lot bro.
Stay safe.

 

[steve the fish]

Thats there to alter the tied results.

Lets take the entire formula and say you have tied values that are also the max value in A1 and A2 for ease. The first one produces:

=IFERROR(ROWS($A$1:$A$200)-RANK(A1,$A$1:$A$200)-COUNTIF($A$1:A1,A1)+2,0)

=IFERROR(200-1-1+2,0)

=IFERROR(200,0)

=200

Then the 2nd one:

=IFERROR(ROWS($A$1:$A$200)-RANK(A2,$A$1:$A$200)-COUNTIF($A$1:A2,A2)+2,0)

=IFERROR(200-1-2+2,0)

=IFERROR(199,0)

=199

 

[Please_H]

Thats there to alter the tied results.

Lets take the entire formula and say you have tied values that are also the max value in A1 and A2 for ease. The first one produces:

=IFERROR(ROWS($A$1:$A$200)-RANK(A1,$A$1:$A$200)-COUNTIF($A$1:A1,A1)+2,0)

=IFERROR(200-1-1+2,0)

=IFERROR(200,0)

=200

Then the 2nd one:

=IFERROR(ROWS($A$1:$A$200)-RANK(A2,$A$1:$A$200)-COUNTIF($A$1:A2,A2)+2,0)

=IFERROR(200-1-2+2,0)

=IFERROR(199,0)

=199

Awesome Technique to tackle the matter.

Thanks for teaching me... Have a great day...

TC....