TheDragonExp
New Member
- Joined
- Dec 18, 2015
- Messages
- 4
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?
<tbody>
</tbody>
<tbody>
</tbody>
My apologies if my question / example is unclear. I appreciate any assistance you can offer.
Happy Holidays!
J
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?
Column A | Column B |
80 | 9.45 |
1100 | 25.65 |
15 | -3.74 |
80 | 8.50 |
60 | 21.58 |
<tbody>
</tbody>
BIN (based on Column B) | Column C |
-10 to 0 | 15 |
0 to 10 | 160 |
10 to 20 | 0 |
20 to 30 | 1160 |
<tbody>
</tbody>
My apologies if my question / example is unclear. I appreciate any assistance you can offer.
Happy Holidays!
J