How do make a "Top seller" list in excel?

R

Robert

I need to have a list of our 5 top selling books based on a list of 20 or so
available books on another tab in excel. How do I do this?
 
R

RagDyeR

Just the titles ... not the amount sold?

Say list on Sheet1, with titles in A1 to A20, and amounts sold in B1 to B20,
try this :

=INDEX(Sheet1!$A$1:$A$20,MATCH(LARGE(Sheet1!$B$1:$B$20,ROWS($1:1)),Sheet1!$B
$1:$B$20,0))

And copy down 5 (or whatever) rows for top selling titles.

If you wanted numbers sold and titles along side,
try this in say C1 of Sheet2:

=LARGE(Sheet1!$B$1:$B$20,ROWS($1:1))

AND, in D1 of Sheet2, enter this:

=INDEX(Sheet1!$A$1:$A$20,MATCH(C1,Sheet1!$B$1:$B$20,0))

Select C1 and D1, and copy down 5 rows or more if desired.

Assuming there are no duplicates.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I need to have a list of our 5 top selling books based on a list of 20 or so
available books on another tab in excel. How do I do this?
 
A

Andre Croteau

As you mentioned, the formula will not work if you have duplicates: one
title will be written twice, with no mention of the other title.
In case you could have some duplicate data, you might want to use the
following formula in an extra column that will give you the ranking
including duplicates.

=RANK(A1,A$1:A$20)+COUNTIF($1:a1,a1)-1 and copy that down

You could then use your formula relating to the new column.

rgds

André
 
Top