We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Histogram in chart wizard

asked 2021-03-11 16:29:12 +0200

ajgpitch gravatar image

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 ;-)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2021-05-02 15:48:58 +0200

rk1981 gravatar image

updated 2021-05-02 15:55:17 +0200

Histogramming 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

edit flag offensive delete link more

Comments

1

Bar 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.

keme gravatar imagekeme ( 2021-05-02 15:59:32 +0200 )edit

Thank you for pointing out the difference,

Indeed 17-9=8, due to trunc used in formula

rk1981 gravatar imagerk1981 ( 2021-05-02 17:47:03 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-03-11 16:29:12 +0200

Seen: 39 times

Last updated: May 02