If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Aggregated charts
Hi friends,
I'm trying to draw a chart which looks like an enhanced histogram chart: 1. I have 3 columns, A, B and C 2. I'll use C as bin to categorize A into different buckets. But I'm not counting A, instead I'll sum B according to the bucket for example: input: A B C 0.5 3.5 1 0.7 3 2 1.2 2.2 3 2.3 1.5 2.5 3 expected output: C SumOfB 1 6.5 2 2.2 3 4.5 M 0 Is there a way to create the chart with existing capability in excel 2003? If not, is there an add-in for download? If I have to write VBA, what would it looks like? Any sugesstion is appreciated, Wei |
#2
|
|||
|
|||
I used an array formula for this. Here is A15 in my worksheet:
0.5 3.5 1 6.5 0.7 3 2 2.2 1.2 2.2 3 4.5 2.3 1.5 2.5 3 The formula in D1 is: =SUM(($A$1:$A$5=C1)*($A$1:$A$5C1-1)*$B$1:$B$5) This is an array formula, which means type it in the cell, then hold Ctrl-Shift while pressing Enter. If you do it right, Excel encloses it in {curly braces}. Fill the formula down to D3 to get the other two sums. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ wz wrote: Hi friends, I'm trying to draw a chart which looks like an enhanced histogram chart: 1. I have 3 columns, A, B and C 2. I'll use C as bin to categorize A into different buckets. But I'm not counting A, instead I'll sum B according to the bucket for example: input: A B C 0.5 3.5 1 0.7 3 2 1.2 2.2 3 2.3 1.5 2.5 3 expected output: C SumOfB 1 6.5 2 2.2 3 4.5 M 0 Is there a way to create the chart with existing capability in excel 2003? If not, is there an add-in for download? If I have to write VBA, what would it looks like? Any sugesstion is appreciated, Wei |
#3
|
|||
|
|||
It works! Thanks a lot!
Wei "Jon Peltier" wrote: I used an array formula for this. Here is A15 in my worksheet: 0.5 3.5 1 6.5 0.7 3 2 2.2 1.2 2.2 3 4.5 2.3 1.5 2.5 3 The formula in D1 is: =SUM(($A$1:$A$5=C1)*($A$1:$A$5C1-1)*$B$1:$B$5) This is an array formula, which means type it in the cell, then hold Ctrl-Shift while pressing Enter. If you do it right, Excel encloses it in {curly braces}. Fill the formula down to D3 to get the other two sums. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ wz wrote: Hi friends, I'm trying to draw a chart which looks like an enhanced histogram chart: 1. I have 3 columns, A, B and C 2. I'll use C as bin to categorize A into different buckets. But I'm not counting A, instead I'll sum B according to the bucket for example: input: A B C 0.5 3.5 1 0.7 3 2 1.2 2.2 3 2.3 1.5 2.5 3 expected output: C SumOfB 1 6.5 2 2.2 3 4.5 M 0 Is there a way to create the chart with existing capability in excel 2003? If not, is there an add-in for download? If I have to write VBA, what would it looks like? Any sugesstion is appreciated, Wei |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Blank Charts on Report opening | AlCamp | Setting Up & Running Reports | 2 | March 26th, 2005 05:36 AM |
Distribute dynamically charts with linked data as stand-alone charts | Richard | Charts and Charting | 2 | March 31st, 2004 05:16 PM |
charts inadvertly resized | Hammer_757 | Charts and Charting | 0 | March 24th, 2004 03:16 AM |
printing multiple charts per page | Pablo | Charts and Charting | 1 | February 27th, 2004 06:37 PM |
Creating a large number of charts automatically (repost) | Francine Kubaka | Charts and Charting | 0 | September 21st, 2003 09:16 PM |