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

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,281
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,281
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
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....
 

Watch MrExcel Video

Forum statistics

Threads
1,123,015
Messages
5,599,351
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top