Returning the top 3 selling items on the menu

**pdhawan** · 11-15-2011, 06:59 PM

Hi All

I am doing some menu engineering in excel. I have a list of menu items in a column and the quantity sold in another. I need to get the top 3 and bottom 3 selling items. The problem arises when the quantities sold are some items. There could be a situation where the top 3 could be more than 3 if say for example 4 items have same quantities that are more than any other item. I have tried formulae like rank, large, index but no luck.

I would appreciate if you guys could help with this. Please let me know if you have any questions.

Thanks

PD

**Azam Ali** · 11-15-2011, 11:54 PM

upload a sample workbook with sample data and your desired results

In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button

**Paul** · 11-16-2011, 12:08 AM

Hi PD, welcome to the forum.

You can use a Pivot Table to quickly discern your top X and bottom X sellers.

Select your data and then click Insert > Pivot Table.

Add the Item field to the Row Values area, then add the # sold to the Values area. You will automatically see a sum of each item sold.

Click the filter button at the top left of the Pivot Table (next to "Row Labels") and choose "Value Filters" > "Top 10...". In the "Top 10 Filter Item" dialog, change 10 to 3 and click OK. That should show you your top 3 selling items - including ties.

You can either change the "Top 10 Filter" dialog to "Bottom" 3 and see those items including ties, or you can just create a second pivot table based on the original data so you can see the top and bottom sellers at the same time. (If you chose to put the Pivot Tables on the same worksheet, of course.)

Hope that helps!

**pdhawan** · 11-16-2011, 12:55 AM

Thanks Paul

I think the pivot table will help. Only problem is I am working on 2003. I did the pivot table but how do I set the filter.

Thanks again