Rank top 5 / Bottom 5 using Rows & Columns

[melvin]

Hi all,

I have attached an excel file where I would like to use the most appropriate formula where I can point to the row & column and get the top and bottom 5 ranks.

Tried Index match but didn't work.


Thanks for your help team.


Regards,

melv.

 

[Khalid NGO]

Hi melvin,

I found only North values; South / East / West are not mentioned in your data.
plus 2 rank columns and FY14/15 columns creating confusions, here is what I understood:

for South TOP 5, you can use this with CSE:
=INDEX(Sheet1!$D$4:$D$19,MATCH(SMALL(IF(Sheet1!$C$4:$C$19=B$1,Sheet1!$A$4:$A$19),ROW(A1)),Sheet1!$A$4:$A$19,0),)


for BOTTOM 5,
replace SMALL with LARGE

Regards,

 

[melvin]

Thanks Khalid,

I did clean the data sheet and tried the formula. But it came with few ranks and few errors

Appreciate ur help.

 

[Deepak]

Thanks Khalid,

I did clean the data sheet and tried the formula. But it came with few ranks and few errors

Appreciate ur help.

U haven't use
CSE = Ctrl+Shift+Enter

 

[Khalid NGO]

Thanks Khalid,

I did clean the data sheet and tried the formula. But it came with few ranks and few errors

Appreciate ur help.

As Deepak Sir advised, you have to press Ctrl Key plus Shift Key plus Enter Key altogether, not just Enter (CSE means this is an array formula)

Try this for your new layout:
=INDEX(Sheet1!$C$4:$C$22,MATCH(SMALL(IF(Sheet1!$B$4:$B$22=B$1,Sheet1!$C$4:$C$22),ROW(A1)),Sheet1!$C$4:$C$22,0),)

Regards,

 

[melvin]

Thanks guys,

But I for sure am doing something wrong? After doing the CSE for array , I get the same results for all the cells.

Apologies guys, sounds dumb but not sure why this is happening.

Melv

 

[Nebu]

Hi:

You are entering it wrongly, do not select entire range and enter, enter it cell-by-cell. Find the attached.

Thanks

 

[melvin]

Thank you all. i got it. But If you are looking at 10 states and ranking to 10 top and bottom, wouldn't it the above be a slower way to do it?

But it does solve my question and I am really appreciate of your patience

Regards,

Melv

 

[melvin]

Gday all,

I have used the array formula which was discussed in this thread. It gives an output, however the output is incorrect.

If I would like to know for each district who is the top performer, the output displayed is incorrect. Would appreciate any assistance.

Regards,

Melv.

 

[NARAYANK991]

Hi ,

Does this not work ?

=LARGE(IF($A$20:$A$352=H$5,$B$20:$B$352),$G6)

Enter this in H6 , as an array formula , using CTRL SHIFT ENTER , and copy across and down.

Narayan

 

[Khalid NGO]

Gday all,

I have used the array formula which was discussed in this thread. It gives an output, however the output is incorrect.

If I would like to know for each district who is the top performer, the output displayed is incorrect. Would appreciate any assistance.

Regards,

Melv.

Hi Melv,
You just need to replace the "SMALL" with "LARGE" and your formula will work.
But you should try the formula given by Narayan Sir, as this is shortest version and without INDEX/MATCH.

Regards,

 

[melvin]

Thanks guy there is an output if I sue the formula, however the output is incorrect.

District N - Rank 1 should be 4474, The array formula comes as 4480.

 

[Khalid NGO]

Thanks guy there is an output if I sue the formula, however the output is incorrect.

District N - Rank 1 should be 4474, The array formula comes as 4480.

Hi Melv,

Formula is giving correct output, i have cross checked:
=MAX(IF(A23:A351="N",B23:B351))
result =4480

Not sure how you get rank 1 as 4474.

 

[melvin]

My bad guys. Column B are profit center. I am ranking% to sales. Should Ipoint the narayan above formula to column C?

 

[Khalid NGO]

My bad guys. Column B are profit center. I am ranking% to sales. Should Ipoint the narayan above formula to column C?

Can you try this:

=INDEX($A$20:$E$352,MATCH(LARGE(IF($A$20:$A$352=H$5,$C$20:$C$352),ROW(A1)),$C$20:$C$352,0),2)

with CSE

 

[melvin]

This is awesome Khalid. I eblieve for bottom 5 I should be doing SMALL

Narayan, Appreciate your assistance.

Regards,

Melv.

 

[Khalid NGO]

This is awesome Khalid. I eblieve for bottom 5 I should be doing SMALL

Narayan, Appreciate your assistance.

Regards,

Melv.

Yes sure you can use SMALL for bottom.
Thanks Dear for the feedback.

Keep visiting.
Regards,