Aggregated charts

[Guest]

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

 

[Jon Peltier]

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$5>C1-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/
_______

 

[Guest]

It works! Thanks a lot!

Wei

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$5>C1-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/
_______