Percentage based histogram

[Confuddled]

Hello,
I am trying to make a histogram based on several percentages. I have a table like this:

Samp Percent
1    51.60
2    6.5
3    45.36
4    87.25
5    55.24
6    12.45
...
... etc.

I would like to see a distribution from 0 to 100 across the x axis while a count of how many fall into specific bins on the y axis.
Rough example:

       _
     /|||\                  _
    /|||||\               /|||\
   0 10 20 30 40 50 60 70 80 90 100 

I am not really sure how to get started.
I have managed to make a very messy column chart but the x axis is always the sample name and the y is always percentage.
Help?

[horst]

I think you can not make a real histogram in Calc. But see the answers to this question.

Main problem I see is that you can’t define bins/buckets in a Calc-Chart.

Lets see if this helpsto do it: Add some columns to your setup.

   B     C    D   E   F
Percent Bin0 Bin1 X      Y-values
85.6    >0   >10  0 -10  =Countif($B$2:$B$100,Bin0)-Countif($B$2:$B$100,Bin1)
6.5     >10  >20  10-20  =Countif($B$2:$B$100,Bin0)-Countif($B$2:$B$100,Bin1)

The idea is to create your bin limits Bin0, Bin1 which double as part of the formula. Column X contains the bin-ranges. Y-values are the number of samples per bin. The first argument of the COUNTIF function has to contain all your samples.

Now you can make a Column-Chart with the x and y values. X-axis will show all bin-ranges -not exactly the %-range 0 to 100- and the length of the column is the number of samples/bin.

[AaronDigulla]

link is broken

[jondo]

The “array formula” FREQUENCY creates histogram data from unsorted raw values.

[Sunday87]

Actually using the FREQUENCY function is much easier than using lots of COUNTIFs and achieves the same thing.

[ROSt53]

@Confuddled @horst,

Why was horst’s proposal not the solution?

• it is needed to define bins to count how many entries are in each bin
• next is to create a column chart with bins at the x-axis and the counts on the y-axis

I did not check horst’s formulas because they look OK to me. I only used a different approach using not if-function and counted thereafter.

I defined 4 bins covering the range >0 and <=12.
For bin1 (>0 and <=3) I used the formula

=IF(AND(B3>0,B3<=3),1,0)

Here is what I did in Calc

and what I got as chart (=histogram):

[rk1981]

After finding Min 9 (cell G13) and Max 57 (G14) of 37 data points in column C, I implement Sturges’ rule for determining bins, i. e. number of intervals G17 with =1+TRUNC(LOG(37,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)