Ask Your Question
0

Percentage based histogram [closed]

asked 2013-02-15 20:51:37 +0100

Confuddled gravatar image

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 flag offensive 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

3 Answers

Sort by » oldest newest most voted
1

answered 2013-02-15 21:24:56 +0100

horst gravatar image

updated 2013-02-17 17:34:49 +0100

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.

edit flag offensive delete link more

Comments

link is broken

Aaron Digulla gravatar imageAaron Digulla ( 2015-05-27 21:37:13 +0100 )edit
1

answered 2013-12-09 13:46:27 +0100

jondo gravatar image

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

edit flag offensive delete link more

Comments

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

Sunday87 gravatar imageSunday87 ( 2016-06-24 14:29:33 +0100 )edit
0

answered 2013-02-17 04:46:34 +0100

ROSt52 gravatar image

@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

and what I got as chart (=histogram):

image description

edit flag offensive delete link more

Question Tools

Stats

Asked: 2013-02-15 20:51:37 +0100

Seen: 34,768 times

Last updated: Dec 09 '13