Dynamic array formula required for placing top 5 records under specific area/name

[Khurrum]

Hello Guys

Need to show only 5 top records dynamically, under specific area/name using an array formula. I extracted the names but it is not showing under specific area, it started from top to bottom order.

 

[prasaddn]

Hi Khurrum,

You have done very good job with spreadsheet. I used your own formula to get you your results.

Type the below formula in Row C35:
=IF(C33>=(LARGE($C$33:$R$33,5)),C33,"")

and drag upto whichever column you need.

Regards,
Prasad DN

 

[NARAYANK991]

Hi Khurrum ,

Two points :

1. Please do not use complicated formulae if the same result can be got by using simpler means. This implies that we should use helper cells / rows / columns where ever possible , if possible ; if there is a genuine constraint that helper cells cannot be used , then the effort to get a single-cell formula is understandable ; otherwise , a blind insistence on single-cell formulae is not correct. In our daily lives , we use a multiplicity of gadgets where we admire simplicity ; we want all possible features but in the simplest form possible , but when it comes to Excel formulae we seem to think that bigger and more complex is better.

To take an example from your workbook , I see the following formula :

=AGGREGATE(9,3,(OFFSET($B2,0,1,1,COLUMNS($C2:$R2))))

I do not know what it does , nor am I interested.

Looking at the column header Totals , I put in the formula :

=SUM($C2:$R2)

and the results seem to be the same ; there may well be a reason to use the AGGREGATE function , but I am sure in the present case the same functionality can be achieved using the SUM function.

2. Can you not have your requirement in the form of conditional formatting ?

See the attached file and comment.

Narayan

 

[Khurrum]

Hello Guys

Need to show only 5 top records dynamically, under specific area/name using an array formula. I extracted the names but it is not showing under specific area, it started from top to bottom order.

Thanks Mr. Parsad DN
Your formula works for me, fine.

 

[Khurrum]

Hi Khurrum ,

Two points :

1. Please do not use complicated formulae if the same result can be got by using simpler means. This implies that we should use helper cells / rows / columns where ever possible , if possible ; if there is a genuine constraint that helper cells cannot be used , then the effort to get a single-cell formula is understandable ; otherwise , a blind insistence on single-cell formulae is not correct. In our daily lives , we use a multiplicity of gadgets where we admire simplicity ; we want all possible features but in the simplest form possible , but when it comes to Excel formulae we seem to think that bigger and more complex is better.

To take an example from your workbook , I see the following formula :

=AGGREGATE(9,3,(OFFSET($B2,0,1,1,COLUMNS($C2:$R2))))

I do not know what it does , nor am I interested.

Looking at the column header Totals , I put in the formula :

=SUM($C2:$R2)

and the results seem to be the same ; there may well be a reason to use the AGGREGATE function , but I am sure in the present case the same functionality can be achieved using the SUM function.

2. Can you not have your requirement in the form of conditional formatting ?

See the attached file and comment.

Narayan

Mr. Narayan

I appreciate your concerns. But the reason for using 'aggregate' instead is, it gives lot of functionality/options, which always helpful in case of error triggers during mapping of long spreadsheets, though it is going away from the usual.

Regards,
Khurrum