# Percentage based histogram [closed]

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?

edit retag reopen merge delete

### Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2015-11-16 14:17:09.140109

Sort by » oldest newest most voted

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.

more

( 2015-05-27 21:37:13 +0200 )edit

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

more

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

( 2016-06-24 14:29:33 +0200 )edit

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

more