• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rank top 5 / Bottom 5 using Rows & Columns

M

melvin

Member
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.
 

Attachments

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 said:
Thanks Khalid,

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

Appreciate ur help.
Click to expand...

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,
 
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
 

Attachments

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
 
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.
 

Attachments

melvin said:
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.
Click to expand...
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,
 
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.
 

Attachments

melvin said:
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.
Click to expand...
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.
 
My bad guys. Column B are profit center. I am ranking% to sales. Should Ipoint the narayan above formula to column C?
 
This is awesome Khalid. I eblieve for bottom 5 I should be doing SMALL

Narayan, Appreciate your assistance.

Regards,

Melv.
 
You must log in or register to reply here.
Back
Top