Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 02 May 2021 17:47:03 +0200Histogram in chart wizardhttps://ask.libreoffice.org/en/question/297775/histogram-in-chart-wizard/Why is there not a histogram option in the chart wizard? This is one of the most common kinds of chart used in data analysis. The best I have found is some long winded approach using the FREQUENCY function. However, often one just wants to quickly check the distribution of some data (to see if it is normal for instance).
There seems a good opportunity to steal a march on a well known competitor here. The histogram options in MS Excel are pretty inflexible. In particular I have not found a way to set specific bin ranges, which one would often like to do with continuous data. The Analysis Toolpak has gaps between columns, which is not appropriate for continuous data.
It would be great to have something available in LibreOffice which was mouse clicks away, but also post-create configurable. That is, it would recognise whether the data was discrete or continuous, automatically choose appropriate bins, but then give opportunity to specify them after.
I teach a university introduction to stats module. It would be great to be able to use LibreOffice Calc, but it is without a histogram wizard, so I have to use Excel (and it's substandard histogram wizard). I like to promote open source software, and this would be an ideal opportunity (and I would not have to boot into a sub-standard operating system ;-)Thu, 11 Mar 2021 16:29:12 +0100https://ask.libreoffice.org/en/question/297775/histogram-in-chart-wizard/Answer by rk1981 for <p>Why is there not a histogram option in the chart wizard? This is one of the most common kinds of chart used in data analysis. The best I have found is some long winded approach using the FREQUENCY function. However, often one just wants to quickly check the distribution of some data (to see if it is normal for instance).</p>
<p>There seems a good opportunity to steal a march on a well known competitor here. The histogram options in MS Excel are pretty inflexible. In particular I have not found a way to set specific bin ranges, which one would often like to do with continuous data. The Analysis Toolpak has gaps between columns, which is not appropriate for continuous data.</p>
<p>It would be great to have something available in LibreOffice which was mouse clicks away, but also post-create configurable. That is, it would recognise whether the data was discrete or continuous, automatically choose appropriate bins, but then give opportunity to specify them after.</p>
<p>I teach a university introduction to stats module. It would be great to be able to use LibreOffice Calc, but it is without a histogram wizard, so I have to use Excel (and it's substandard histogram wizard). I like to promote open source software, and this would be an ideal opportunity (and I would not have to boot into a sub-standard operating system ;-)</p>
https://ask.libreoffice.org/en/question/297775/histogram-in-chart-wizard/?answer=307312#post-id-307312Histogramming is pretty easy with hands:
Statistics on data shows Min 9 (cell `G13`) and Max 57 (`G14`) of 37 data points or 'counts' (`G16`) in column C
I implement Sturges' rule for determining bins, i. e. number of intervals `G17` with `=1+TRUNC(LOG(G16,2))` and generate column of bins with `=$G$13+TRUNC((ROW()-11)*($G$14-$G$13)/$G$17)` filled with I11=8 (!NB MIN-1 and is skipped) 17 25 33 41 49 57
For data labeling I fill next column with `=TEXTJOIN($H$1,1,I11+1,I12)` where `H1` is a cell with " `–` " separator to make the whole cell look like 9 – 17 ... 50 – 57.
Finally `F 12:17` cells I fill with array function `=FREQUENCY($C$1:$C$37,$I$12:$I$17)`
![image description](/upfiles/16199631182862573.png)Sun, 02 May 2021 15:48:58 +0200https://ask.libreoffice.org/en/question/297775/histogram-in-chart-wizard/?answer=307312#post-id-307312Comment by rk1981 for <p>Histogramming is pretty easy with hands:</p>
<p>Statistics on data shows Min 9 (cell <code>G13</code>) and Max 57 (<code>G14</code>) of 37 data points or 'counts' (<code>G16</code>) in column C</p>
<p>I implement Sturges' rule for determining bins, i. e. number of intervals <code>G17</code> with <code>=1+TRUNC(LOG(G16,2))</code> and generate column of bins with <code>=$G$13+TRUNC((ROW()-11)*($G$14-$G$13)/$G$17)</code> filled with I11=8 (!NB MIN-1 and is skipped) 17 25 33 41 49 57</p>
<p>For data labeling I fill next column with <code>=TEXTJOIN($H$1,1,I11+1,I12)</code> where <code>H1</code> is a cell with " <code>–</code> " separator to make the whole cell look like 9 – 17 ... 50 – 57.</p>
<p>Finally <code>F 12:17</code> cells I fill with array function <code>=FREQUENCY($C$1:$C$37,$I$12:$I$17)</code>
<img src="/upfiles/16199631182862573.png" alt="image description"></p>
https://ask.libreoffice.org/en/question/297775/histogram-in-chart-wizard/?comment=307340#post-id-307340Thank you for pointing out the difference,
Indeed 17-9=8, due to trunc used in formulaSun, 02 May 2021 17:47:03 +0200https://ask.libreoffice.org/en/question/297775/histogram-in-chart-wizard/?comment=307340#post-id-307340Comment by keme for <p>Histogramming is pretty easy with hands:</p>
<p>Statistics on data shows Min 9 (cell <code>G13</code>) and Max 57 (<code>G14</code>) of 37 data points or 'counts' (<code>G16</code>) in column C</p>
<p>I implement Sturges' rule for determining bins, i. e. number of intervals <code>G17</code> with <code>=1+TRUNC(LOG(G16,2))</code> and generate column of bins with <code>=$G$13+TRUNC((ROW()-11)*($G$14-$G$13)/$G$17)</code> filled with I11=8 (!NB MIN-1 and is skipped) 17 25 33 41 49 57</p>
<p>For data labeling I fill next column with <code>=TEXTJOIN($H$1,1,I11+1,I12)</code> where <code>H1</code> is a cell with " <code>–</code> " separator to make the whole cell look like 9 – 17 ... 50 – 57.</p>
<p>Finally <code>F 12:17</code> cells I fill with array function <code>=FREQUENCY($C$1:$C$37,$I$12:$I$17)</code>
<img src="/upfiles/16199631182862573.png" alt="image description"></p>
https://ask.libreoffice.org/en/question/297775/histogram-in-chart-wizard/?comment=307315#post-id-307315Bar chart, but not histogram.
- A histogram is used for continuous data. Bars should touch to reflect this.
- Bins may be different size. Width of bar should reflect bin size.
- Heigth of bar signifies **frequency**, i.e. (roughly) count/binsize. Not simply the count.
Your first bin is larger than the rest, but I suspect this is by accident. The width looks equal on all bars.Sun, 02 May 2021 15:59:32 +0200https://ask.libreoffice.org/en/question/297775/histogram-in-chart-wizard/?comment=307315#post-id-307315