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

#### Zakky

##### Board Regular
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.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Fluff

##### MrExcel MVP, Moderator
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

##### MrExcel MVP, Moderator
Excel Formula:
``=IF(AND(A1>=1,A1<=5),"Gain", IF(A1>MAX(\$A\$1:\$A\$1000)-5,"Loss",""))``

#### Zakky

##### Board Regular
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

##### Board Regular
Excel Formula:
``=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

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
5
Views
190
Replies
2
Views
273
Replies
0
Views
293
Replies
1
Views
445
Replies
1
Views
125

1,195,993
Messages
6,012,745
Members
441,724
Latest member
Aalbid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back