3.140 FAQ-837 How to group data into bins and sum up the data of each bins respectively?

how_to_group_data_into_bins_and_sum_up_the_data_of_each_bin_respectively

Last Update: 9/22/2017

If you want to group your data into bins and calculate the sum of data in each bin respectively, as a workaround, you can group the data by setting group numbers for data at first, and then do statistics on groups.

It is supposed that there is a data that is from 1 to 20 in Column A, you can follow the steps as below:

1. Add a new column Column B, and enter the values of bin ends in Column B.
   For example, if you want to group data into the bins that are [0, 5), [5, 10), [10, 15) and [15, 20), you can enter 0, 5, 10, 15 and 20 in each row of Column B.

   

2. Add a new column Column C and set its long name to GroupNumber.
3. Highlight Column C, and then right click to select Set Column Values… in context menu to launch Set Value dialog.
4. Enter Index(col(A),col(B),1) in the Column Formula edit box.

   

   Note: To learn more about the function Index, please refer to LabTalk Guide.

5. Click OK button, and the group numbers will show up in Column C.
   In this case, group number 1 indicates that this data point is in the bin of [0, 5), group number 2 indicates that this data point is in the bin of [5, 10) and so on.

   

6. Highlight Column A, and then select Statistics: Descriptive Statistics: Statistics on Columns from Origin menu to open Statistics on Columns dialog.
7. Click the triangle button of Group item in the dialog and select Column C in the flyout.

   

8. Click OK button, the sum of each bin can be found in the result sheet DescStatsQuantities1.

   

Keywords:Sum, Index, Set column values, Descriptive statistics