Sorting Data into Bins in Excel 2010

[TheDragonExp]

Hello,

I am trying to sort data I have into bins, preferably through some sort of formula. I have tried looking through this website (and others) but can't seem to find a solution. Any help would be greatly appreciated.

Ok, so I have two columns. The first column (say Column A) has a number of values between 0 & ~1200. The second column (Column B) has a number of values between -10 & 80. What I want to do is sort this data into bins based on Column B's values (-10 to 0, 0 to 10, 10 to 20, ... , 70 to 80). Each value from Column A would fit into a bin based on its Column B value. I then need the column A values to sum to a total value for each bin (in a separate Column).

Perhaps a simple example would help illustrate what I am trying to do... So say there is my data in Column A & B, what I want to do is in a separate column (Column C), have Column A's data sorted into Column C based on Column B's values (whichever range it falls into). I would also like the Column A values to sum up in Column C. I have illustrated the answers in the Column C table below. So for BIN '0 to 10', there are two values that fall into that range (8.50 & 9.45), so the total is 160 (80 + 80). Is there anyway to do this by entering some formula into Column C?

[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]9.45[/TD]
[/TR]
[TR]
[TD]1100[/TD]
[TD]25.65[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]-3.74[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]8.50[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]21.58[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD]BIN (based on Column B)[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]-10 to 0[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]0 to 10[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]10 to 20[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]20 to 30[/TD]
[TD]1160[/TD]
[/TR]
</tbody>[/TABLE]



My apologies if my question / example is unclear. I appreciate any assistance you can offer.

Happy Holidays!
J

 

[JoeMo]

You could use an array formula like this:

Excel Workbook
	A	B	C	D	E
1	80	9.45		-10 to 0	15
2	1100	25.65		0 to 10	160
3	15	-3.74		10 to 20	0
4	80	8.5		20 to 30	1160
5	60	21.58
Sheet13

 

[Peter_SSs]

For those particular circumstances, possibly also this, copied down.

Excel Workbook
	A	B	C	D	E
1	Sum Value	Value		Range	Sum
2	80	9.45		-10 to 0	15
3	1100	25.65		0 to 10	160
4	15	-3.74		10 to 20	0
5	80	8.5		20 to 30	1160
6	60	21.58
Sums

Note: There could be an issue if a value in column B is at the join of one of the ranges, as you haven't made clear what should happen there. For example, if 10 appears in column B, does it belong to '0 to 10' or to '10 to 20'?

 

[TheDragonExp]

Thank you very much Joe! The formula you provided worked perfectly

Peter - The formula Joe provided worked! Thank you for your reply as well