Challenging question

[ddl8]

Hi guys,

Is there a formula that allows you to find say 4th biggest number in data that is not filtered by size?

# Value
Z comp 20
Y comp 30
A comp 5
T comp 15

I would need that cell would show Y comp. Is there any excel formula for that purpose? I know I can manually filter and look for 4th biggest number but I need it for a lot of different excell sheets so it will eat up a lot of time.

Any suggestions?

Thanks

 

[Ruddles]

=LARGE([I]range[/I],4)

 

[GlennUK]

Look at the LARGE function.

 

[scottylad2]

Hi guys,

Is there a formula that allows you to find say 4th biggest number in data that is not filtered by size?

# Value
Z comp 20
Y comp 30
A comp 5
T comp 15

I would need that cell would show Y comp. Is there any excel formula for that purpose? I know I can manually filter and look for 4th biggest number but I need it for a lot of different excell sheets so it will eat up a lot of time.

Any suggestions?

Thanks

Your posts a little confusing, surely Y Comp is the largest? 30 is the biggest number?

If it's trully the 4th largest you need then change the 1 to 4 in the following formula

Excel Workbook
	D	E
24	Z Comp	20
25	Y Comp	30
26	A Comp	5
27	T Comp	15
28
29	Y Comp
Sheet1

 

[jeffreybrown]

Hello & Welcome to the Board,

Isn't the 30 actually the largest?

How about if you want to return the "y"...

=INDEX(A1:A4,MATCH(LARGE(C1:C4,1),C1:C4,0))

 

[GlennUK]

If the OP actually meant the 4th smallest number, then the SMALL function would do it.

 

[ddl8]

Your posts a little confusing, surely Y Comp is the largest? 30 is the biggest number?

If it's trully the 4th largest you need then change the 1 to 4 in the following formula

Excel Workbook
	D	E
24	Z Comp	20
25	Y Comp	30
26	A Comp	5
27	T Comp	15
28	*	*
29	Y Comp	*
Sheet1

Thanks man, it worked perfectly! Exactly what I was looking for!

 

[Peter_SSs]

Welcome to the MrExcel board!

Thanks man, it worked perfectly! Exactly what I was looking for!

In that case it would appear that you were looking for the largest value, not the 4th largest value. That brings me to ..

One comment:
=INDEX(D24:D27,MATCH(MAX(E24:E27),E24:E27,0))
is probably a bit simpler.

And one question:
Is it possible that the largest number occurs more than once on the right, and if so, what result(s) would you want returned? eg This data

Excel Workbook
	D	E
24	Z Comp	20
25	Y Comp	30
26	A Comp	5
27	T Comp	30
Match Max