How to find top 5 values from data using function

[Merrysa]

Hi All,
Hope you are doing great, i have a data where i have to found top 5 values, could you please let me how to resolve it. thanks
attached is the data sheet

[menem]

Please try.

B5
=INDEX(Data!$A$1:$A$11,AGGREGATE(15,6,(ROW(Data!$A$1:$A$11)-(ROW(Data!$A$1)-1))/(Data!$B$1:$B$11=C5),COUNTIF($C$4:$C5,$C5)))

C5
=LARGE(Data!$B$1:$B$11,ROWS(C$4:C4))

Regards.

[wk9128]

worksheet name : Result

Cell B5 array formula , Drag down and across

[Merrysa]

@wk9128
Thanks, It is giving Name where there is no data for any name in data sheet, have attached the scenario.

[Merrysa]

@menem
what actually "AGGREGATE(15,6," this is doing? how can i customize it in large data?

[wk9128]

@Merrysa
array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula

[JohnTopley]

@Merrysa
Please post a more realistic sample of your data as the first file obviously did not represent your true situation.

[Merrysa]

@Merrysa
array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula

Sorry i am not able to do, can you send me the screen shot how it will look like.

[wk9128]

Please see POST#6 attachment and picture

[Merrysa]

Please see POST#6 attachment and picture

Thanks, but why i am getting NZ and SA as 0 where blank is in Data sheet. attached the both images

It shouldn't show NZ and SA is in result sheet if there is no data for those name.

[AliGW]

Because there IS data for them - they have 0, not a blank cell.

[menem]

AGGREGATE( 15 = min , 6 = ignore all errors , .... list of array to verify .... , no of array that not error )

but list of array to verify is hard for me to explain (due to my english)

AGGRIEGATE( 15 , 6 , { 50, 60 , #N/A , 9 , #DIV/0 , 1 } , 3 ) will give result 50 because 50 is small value number 3 exclude errors

Regards.

[wk9128]

worksheet name : Result

Pls Click the left mouse button to select the ranges area B5:B9 ; pls Place the following formula in Formula Bar , then Array formula

Then Copy ranges area B5:B9 to C5

[kvsrinivasamurthy]

In B5

Please Login or Register  to view this content.

In C5

Please Login or Register  to view this content.