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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

MisterCrash

Active Member
Joined
Jan 27, 2003
Messages
279
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.
 

rob2505

New Member
Joined
Jul 23, 2010
Messages
17
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 ?
 

MisterCrash

Active Member
Joined
Jan 27, 2003
Messages
279
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,156
Messages
5,657,158
Members
418,363
Latest member
Debating_Earth

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
Top