How do I pick out top 5 and bottom five from a long list

[Zakky]

Hello. I have, say, used RANK in column A to rank 100 products profitability. In column B I have used the formula next to each rank number: IF(AND(A1>=1,A1<=5),"Gain","") to pick out top 5 performers i.e. 1,2,3,4,5 from the ranked list (1 being the best). I now want to also pick out the top 5 worst products in the same column B i.e 96,97,98,99,100 (with 100 being the worst). I would like to call these 'Loss'. So in total only 10 cells will be populated in column B and all others will be empty/blank. Is it even possible to do this in same formula/cell. I don't know how to identify the top 5 best and worst in same formula. I have used 100 as an example but the number of products changes each month, so next month there could be 237 products ranked from 1 to 237. Then top 5 would be 1,2,3,4,5 and worst 234, 235,236,237,238 (238 being the worst). Thank in advance.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

Thanks for updating your profile, how about

=IF(AND(A1>=1,A1<=5),"Gain", IF(A1>MAX($A$1:$A$1000)-5,"Loss",""))

 

[Zakky]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

hi, thanks for the prompt reply. i'm using Office 365, version 2111

 

[Zakky]

Thanks for updating your profile, how about

=IF(AND(A1>=1,A1<=5),"Gain", IF(A1>MAX($A$1:$A$1000)-5,"Loss",""))

WOW! you hit the mark first time. Very impressive! It works like a dream. Thank you!!!

 

[Fluff]

You're welcome & thanks for the feedback.