Generate the sum of values in a histogram bin

[JestPhulin]

I have a table of sales, with each row being a time of sale and a sales amount. For example:

1:00    5.25
1:15    2.77
2:30    5.15
2:45    8.00
3:15    4.27

I want to be able to generate a chart showing the total sales by hour. Something that will export

1:00-1:59  8.02
2:00-2:59  13.15
3:00-3:59  4.27

I know the FREQUENCY function will create bins, but that will just show the number of sales I had at each hour, not the sum of the second column. Is there an array function (or work-around) that will: 1) take a table that can be sorted, 2) check to see if the value in column A is in a certain range, 3) add the value in column B to a sum specific to each range, and 4) output that sum?

Thanks in advance, and apologies if asked before – my search skills weren’t able to find anything like this.

[SM_Riga]

Hello @JestPhulin

It is possible to use a combination of functions for such task, but you also can create a PivotTable to achieve the goal mentioned. I have created a sample spreadsheet and will describe the process.

Step 1. The most important. Prepare source data. As dates/time in Calc are represented as numbers (dates) and fractions of the day (time), you need to correctly prepare data for processing. If in the source data time is represented as text string, you can use TIMEVALUE function to convert it to the internal numeric representation of the time value. Conversion depends on locale. So in the end all selling times should be represented as fractions between 0 and 1. Then you need to format these cells as time, for example HH:MM for comfortable representation of values. This is a must for correct data processing/interval grouping. Be aware that all Date functions will treat these values as date of 1899-12-30 (mostly, depends on Calc settings)

Step 2. Click on any cell inside your source data and go to menu Data -> Pivot table -> Create... The whole source range will be selected. In dialog you can chose Current selection and click Ok (I use previously defined Named Range as source in my sample)

Step 3. Pivot Table Layout dialog will appear. Drag Time from Available Fields to Row Fields and Amount to Data Fields Yous can double-click on Sum-Amount entry and adjust function applied. You can also select destination place for created table and other details. Click Ok to create Pivot Table.

Step 4. You will get a table with almost every sale on its own row, only those with exactly the same source time value will be combined already.

Step 5. So now you need to group times by intervals. In created Pivot Table Click on any cell representing time and go to menu item Data -> Group and Outline -> Group... In dialog select Group by -> Intervals -> Hours

Step 6. Enjoy the result.

The created table is interactive. When source data changes, you just right-click on it, select Refresh and contents will be recalculated based on new data. You can also link chart to PivotTable data or query data with GETPIVOTDATA function.

[JestPhulin]

This looks wonderful. Thank you for the detailed explanation. When I have a chance to actually implement it I will mark it as a correct answer.

---

And, close enough to what I wanted :) There are some tweaks I need to do because of different versions, (I'm using 4.2.8.2 for Linux), but this definitely puts me on the right path.