Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenThu, 17 Jan 2019 09:33:05 +0100Generate the sum of values in a histogram binhttps://ask.libreoffice.org/en/question/179281/generate-the-sum-of-values-in-a-histogram-bin/ 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.Mon, 14 Jan 2019 12:42:58 +0100https://ask.libreoffice.org/en/question/179281/generate-the-sum-of-values-in-a-histogram-bin/Answer by SM_Riga for <p>I have a table of sales, with each row being a time of sale and a sales amount. For example:</p>
<pre><code>1:00 5.25
1:15 2.77
2:30 5.15
2:45 8.00
3:15 4.27
</code></pre>
<p>I want to be able to generate a chart showing the total sales by hour. Something that will export</p>
<pre><code>1:00-1:59 8.02
2:00-2:59 13.15
3:00-3:59 4.27
</code></pre>
<p>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?</p>
<p>Thanks in advance, and apologies if asked before -- my search skills weren't able to find anything like this.</p>
https://ask.libreoffice.org/en/question/179281/generate-the-sum-of-values-in-a-histogram-bin/?answer=179660#post-id-179660Hello @Jest Phulin
It is possible to use a combination of functions for such task, but you also can create a [PivotTable](https://help.libreoffice.org/Calc/Pivot_Table_2) to achieve the goal mentioned. I have created a [sample spreadsheet](/upfiles/15476794324238454.ods) and will describe the process.
Step 1. The most important. Prepare source data. As [dates/time in Calc](https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_Date_%26_Time_functions) 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](https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_TIMEVALUE_function) to convert it to the internal numeric representation of the time value. Conversion depends on [locale](https://ask.libreoffice.org/en/question/121650/calc-how-to-add-time-increments/?answer=121652#post-id-121652). 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)
![time_format](/upfiles/15476766294005371.png)
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)
![create_pivot](/upfiles/15476774487725013.png)
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.
![pivot_data](/upfiles/1547677734132797.png)
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.
![pivot_ungrouped](/upfiles/15476784833863044.png)
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`
![pivot_grouping](/upfiles/15476787066272098.png)
Step 6. Enjoy the result.
![pivot_grouped](/upfiles/15476787639831575.png)
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.
Wed, 16 Jan 2019 23:57:20 +0100https://ask.libreoffice.org/en/question/179281/generate-the-sum-of-values-in-a-histogram-bin/?answer=179660#post-id-179660Comment by Jest Phulin for <p>Hello <a href="/en/users/57494/jest-phulin/">@Jest Phulin</a></p>
<p>It is possible to use a combination of functions for such task, but you also can create a <a href="https://help.libreoffice.org/Calc/Pivot_Table_2">PivotTable</a> to achieve the goal mentioned. I have created a <a href="/upfiles/15476794324238454.ods">sample spreadsheet</a> and will describe the process.</p>
<p>Step 1. The most important. Prepare source data. As <a href="https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_Date_%26_Time_functions">dates/time in Calc</a> 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 <code>TIMEVALUE</code> <a href="https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_TIMEVALUE_function">function</a> to convert it to the internal numeric representation of the time value. Conversion depends on <a href="https://ask.libreoffice.org/en/question/121650/calc-how-to-add-time-increments/?answer=121652#post-id-121652">locale</a>. 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 <code>HH:MM</code> 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)
<img alt="time_format" src="/upfiles/15476766294005371.png"></p>
<p>Step 2. Click on any cell inside your source data and go to menu <code>Data -> Pivot table -> Create...</code> The whole source range will be selected. In dialog you can chose <code>Current selection</code> and click <code>Ok</code> (I use previously defined Named Range as source in my sample)
<img alt="create_pivot" src="/upfiles/15476774487725013.png"></p>
<p>Step 3. Pivot Table Layout dialog will appear. Drag Time from <code>Available Fields</code> to <code>Row Fields</code> and Amount to <code>Data Fields</code> Yous can double-click on <code>Sum-Amount</code> entry and adjust function applied. You can also select destination place for created table and other details. Click Ok to create Pivot Table.
<img alt="pivot_data" src="/upfiles/1547677734132797.png"></p>
<p>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.
<img alt="pivot_ungrouped" src="/upfiles/15476784833863044.png"></p>
<p>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 <code>Data -> Group and Outline -> Group...</code> In dialog select <code>Group by -> Intervals -> Hours</code>
<img alt="pivot_grouping" src="/upfiles/15476787066272098.png"></p>
<p>Step 6. Enjoy the result.
<img alt="pivot_grouped" src="/upfiles/15476787639831575.png"></p>
<p>The created table is interactive. When source data changes, you just right-click on it, select <code>Refresh</code> and contents will be recalculated based on new data. You can also link chart to PivotTable data or query data with <code>GETPIVOTDATA</code> function.</p>
https://ask.libreoffice.org/en/question/179281/generate-the-sum-of-values-in-a-histogram-bin/?comment=179680#post-id-179680This 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.
<hr>
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.Thu, 17 Jan 2019 09:33:05 +0100https://ask.libreoffice.org/en/question/179281/generate-the-sum-of-values-in-a-histogram-bin/?comment=179680#post-id-179680