Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 24 Jun 2016 14:29:33 +0200Percentage based histogramhttps://ask.libreoffice.org/en/question/12192/percentage-based-histogram/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? Fri, 15 Feb 2013 20:51:37 +0100https://ask.libreoffice.org/en/question/12192/percentage-based-histogram/Answer by jondo for <p>Hello,
I am trying to make a histogram based on several percentages. I have a table like this:</p>
<pre><code>Samp Percent
1 51.60
2 6.5
3 45.36
4 87.25
5 55.24
6 12.45
...
... etc.
</code></pre>
<p>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:</p>
<pre><code> _
/|||\ _
/|||||\ /|||\
0 10 20 30 40 50 60 70 80 90 100
</code></pre>
<p>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? </p>
https://ask.libreoffice.org/en/question/12192/percentage-based-histogram/?answer=26969#post-id-26969The "array formula" [FREQUENCY](https://help.libreoffice.org/Calc/Array_Functions#FREQUENCY) creates histogram data from unsorted raw values.Mon, 09 Dec 2013 13:46:27 +0100https://ask.libreoffice.org/en/question/12192/percentage-based-histogram/?answer=26969#post-id-26969Comment by Sunday87 for <p>The "array formula" <a href="https://help.libreoffice.org/Calc/Array_Functions#FREQUENCY">FREQUENCY</a> creates histogram data from unsorted raw values.</p>
https://ask.libreoffice.org/en/question/12192/percentage-based-histogram/?comment=72348#post-id-72348Actually using the `FREQUENCY` function is much easier than using lots of `COUNTIF`s and achieves the same thing.Fri, 24 Jun 2016 14:29:33 +0200https://ask.libreoffice.org/en/question/12192/percentage-based-histogram/?comment=72348#post-id-72348Answer by horst for <p>Hello,
I am trying to make a histogram based on several percentages. I have a table like this:</p>
<pre><code>Samp Percent
1 51.60
2 6.5
3 45.36
4 87.25
5 55.24
6 12.45
...
... etc.
</code></pre>
<p>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:</p>
<pre><code> _
/|||\ _
/|||||\ /|||\
0 10 20 30 40 50 60 70 80 90 100
</code></pre>
<p>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? </p>
https://ask.libreoffice.org/en/question/12192/percentage-based-histogram/?answer=12195#post-id-12195I think you can not make a real histogram in Calc. But see the answers to [this question](http://ask.libreoffice.org/en/question/17/how-do-you-plot-a-histogram-in-libreoffice-calc/).
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.
</p><pre><code> 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)
</code></pre><p>
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.Fri, 15 Feb 2013 21:24:56 +0100https://ask.libreoffice.org/en/question/12192/percentage-based-histogram/?answer=12195#post-id-12195Comment by Aaron Digulla for <p>I think you can not make a real histogram in Calc. But see the answers to <a href="http://ask.libreoffice.org/en/question/17/how-do-you-plot-a-histogram-in-libreoffice-calc/">this question</a>.</p>
<p>Main problem I see is that you can't define bins/buckets in a Calc-Chart.</p>
<p>Lets see if this helpsto do it: Add some columns to your setup.</p>
<p></p><pre><code> 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)
</code></pre><p>
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.</p>
<p>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.</p>
https://ask.libreoffice.org/en/question/12192/percentage-based-histogram/?comment=51201#post-id-51201link is brokenWed, 27 May 2015 21:37:13 +0200https://ask.libreoffice.org/en/question/12192/percentage-based-histogram/?comment=51201#post-id-51201Answer by ROSt52 for <p>Hello,
I am trying to make a histogram based on several percentages. I have a table like this:</p>
<pre><code>Samp Percent
1 51.60
2 6.5
3 45.36
4 87.25
5 55.24
6 12.45
...
... etc.
</code></pre>
<p>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:</p>
<pre><code> _
/|||\ _
/|||||\ /|||\
0 10 20 30 40 50 60 70 80 90 100
</code></pre>
<p>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? </p>
https://ask.libreoffice.org/en/question/12192/percentage-based-histogram/?answer=12301#post-id-12301@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
![image description](/upfiles/13610726844265924.gif)
and what I got as chart (=histogram):
![image description](/upfiles/13610727327056473.gif)
Sun, 17 Feb 2013 04:46:34 +0100https://ask.libreoffice.org/en/question/12192/percentage-based-histogram/?answer=12301#post-id-12301