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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,997
Messages
5,508,664
Members
408,689
Latest member
SamSan78

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top