Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do make a "Top seller" list in excel?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do make a "Top seller" list in excel?
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$2 0,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! ================================================== === "Robert" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do make a "Top seller" list in excel?
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é "RagDyeR" wrote in message ... 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$2 0,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! ================================================== === "Robert" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a Validation List in Excel, using data on a second | Excel Discussion (Misc queries) | |||
How do I create a List in Excel 2000 | Excel Worksheet Functions | |||
How do I make longer a drop-down list (the window) in excel? | Excel Worksheet Functions | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
Is there a way to make a drop down list in Excel that lets you se. | Excel Worksheet Functions |