Charting usage by top 5 clients

rob2505

New Member
Joined
Jul 23, 2010
Messages
17
Hi,

I would like to know if there is a method by which I can chart the top 5 clients week on week, and all remaining clients as "other". I currently chart this weekly with all clients, so my chart can look quite busy - using Stacked Area. I've tried using filters but these numbers are also used as references to other parts of the sheet, so cannot hide them and Top 10 won't give me a summary of the remaining clients. I'm using excel 2007.

.............29-Jul.....05-Aug....12-Aug... etc
LONDON..123555...15563......11117
CAIRO.....0...........123333....0
PARIS.....999211....5667.......0
OSLO......1100.......43856.....7000
LUANDA...66610.....0............295611
TRIPOLI...88488.....147933....111
etc

Is it possible or will my boss just have to live with the current chart
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
You can isolate the top 5 clients by using the LARGE, INDEX, and MATCH functions (provided there are no ties).

Let's suppose the client names are in cells A3:A100, and their values are in cells B3:B100. Further suppose that you want the top 5 client names to be in cells D3:D7, and their values in cells E3:E7. The value for the othe clients will be kept in cell E8.

1. To get the value for the largest client, enter this formula in cell E3:

=LARGE($B$3:$B$100, 1)


2. To get the largest client's name, use this formula in cell D3:

=INDEX($A$3:$A$100, MATCH(E3, $B$3:$B$100, 0))


3. Copy the formula from cell E3 down to E4, but change it to:

=LARGE($B$3:$B$100, 2)

Likewise, amend this formula for cells E5:E7


4. Copy the formula in cell D3 down through D7.


5. In cell E8, put this formula to get the sum for the remaining clients:

=SUM($B$3:$B$100)-SUM(D3:D7)


6. Use the information in cells D3:E8 for your chart.
 
Upvote 0
Thanks, this does work for one week, but the next week they may not be the same top 5, what I'm looking for is the top 5 clients across a range of weeks, ( so the values for each client are B3:AB100 ) then chart those top 5 clients weekly and remaining clients as "other" clients.

Hope this makes sense ?
 
Upvote 0
1. Move the section that gets the values for the top 5 clients to the bottom of each column (let's say rows 102 to 107). Put the section with the names of the clients right below that (rows 108 to 113).

2. In a separate area of the worksheet, have a cell where you can enter the date of interest. Let's call it cell B120.

3. In cells B122 to B127, have the names of the top 5 clients for the week (and the others). Use this formula for cell B122, and copy it down. This presumes the dates are in row 2.

=INDEX($B108:$AB108, MATCH($B$120, $B$2:$AB$2, 0)

4. In cells C122 to C127, do the same thing for the values:

=INDEX($B102:$AB102, MATCH($B$120, $B$2:$AB$2, 0)

5. Finally, base your graph on those cells.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top