Finding the highest and lowest numbers

[bines53]

Hello Friend !
I want to get the five cells with the highest numbers and the lowest, the location of
These cells.

Thank you !

 

[Mike H..]

Hello Friend !
I want to get the five cells with the highest numbers and the lowest, the location of
These cells.

Thank you !

Hi,

The highest numbers are below.

What would you like to return for each of these?
Similarly, what do you want for the 5 smallest numbers, with repeats there are 22 of those?


24
22
21
21
21
21
21
20
20
20
19
19
19

 

[bines53]

Hi Mike ,
I am looking for the formula cell address,For example 24 = M3
Or answer it is a combination of two numbers 12 and 2
Thank you !
,

 

[Mike H..]

Hi Mike ,
I am looking for the formula cell address,For example 24 = M3
Or answer it is a combination of two numbers 12 and 2
Thank you !
,

Hi,

Struggled with this because of the duplicate numbers but this seems to work, 2 new columns. 'Larger to small' and the number at that address.

 

[bines53]

Hi Mike ,
Thanks for the great solution!
Is there a way to solve instead of getting the answer M3, have 12 and 2?

David,

Thank you !

 

[Mike H..]

Hi Mike ,
Thanks for the great solution!
Is there a way to solve instead of getting the answer M3, have 12 and 2?

David,

Thank you !

Hi,

There may be but right now in the UK it's very late and I'm going to bed. I'll look again tomorrow.

 

[Somendra Misra]

Hi David,

See your file, colored cell has formula in it.

Regards,

 

[bines53]

Hi Somendra,

A perfect solution!!!

Thank you !

 

[bines53]

Hi Somendra,

I checked with ten cells with the highest numbers and the lowest,And I have a problem with the formulas of the row.I emphasized some of them with black font.

Thank you !

David

 

[NARAYANK991]

Hi David ,

See if this is OK.

Narayan

 

[Somendra Misra]

Hi David,

Sorry for the mistake, please check this file.

Regards,

 

[bines53]

Hi Somendra,

I put other data in column 20, I discovered the formulas do not work .

Thank you !

David

 

[bines53]

Hi Narayan,

A perfect solution!!!

Thank you !

 

[Somendra Misra]

Hi David,

I tried to check some possibility with the initial formula but missed this one. See this file it take care of it. Just check for other possibilities.

Regards,

 

[bines53]

Hi Somendra,

Now it works great !
Thanks for your support and patience !

Thank you !

David

 

[bines53]

Hi Somendra,

I want to convert the formula of the column, to function AGGREGATE,
with the row I was able to convert.

Thank you !

 

[Somendra Misra]

Try in Z2:

=AGGREGATE(14,6,((X2=INDEX($B$2:$U$21,Y2,))*(COLUMN($B$1:$U$1)-COLUMN($B$1)+1)),COUNTIFS(Y$2:Y2,Y2,X$2:X2,X2))

Regards,

 

[bines53]

Hi Somendra,

Beautiful!

There is little difference, now, when the two values are equal in a row, it returns the first result of the far column.

Thank you !

David

 

[bines53]

Hi Somendra,

I made a small change,
=AGGREGATE(15,6,($B$1:$U$1)/($B$2:$U$21=X2)/($A$2:$A$21=Y2),COUNTIFS(Y$2:Y2,Y2,X$2:X2,X2))

Thanks for your help !

David