Benches Uncategorized

7

I need to create a histogram...

apple Verified

Imported from the Apple Support Forums via the Wayback Machine 🔗

Hi folks,

I am in stats. I need to create histograms.

LAME excel 2008 does not allow me to. I cannot find the option in NUMBERS.

If numbers cannot create histograms, is there any mac software that CAN?

Thanks!

7 replies

Sign in or join Beachball Help to reply.

  1. 0

    Thomas Wright1

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Are you asking how to set up spreadsheet tables to gather raw data into appropriate groups or are you asking how to make a histogram graph from the data? As far as I know, Numbers doesn't have a specific template for creating a histogram but it's not difficult to gather data into sets bounded by values you specify, then count the number in each set, then construct a bar chart of the relative values of each set.

  2. 0

    Badunit

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    I don't know of any Mac program dedicated to histograms and charting but I'm sure there are more than a few math packages out there.

    Within Numbers (or Excel) you can use the FREQUENCY function to bin your data. Then you plot the binned data. It is very simple to do and your bins can be whatever size(s) you choose. Take a look at the Formulas and Functions Guide to see how to use the FREQUENCY function. If you need any help getting it to work, come on back.

  3. 0

    Eric Hadley-Ives

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    It’s true that Numbers doesn’t have a function to do this, but it’s pretty easy to put together a series of a few tables and create a histogram in Numbers. I do this to show score distributions to my students on tests I give them.

    Here are the steps.

    First. I have the scores I want to put into a histogram in a column in my first table. For this example, let’s say my first table is named “scores” and there are columns for each variable. It so happens that the first variable I'm using here is a relationship test score. So, the first column has the label “Relationships” in the first row, and then below that down the column are rows of scores on my relationship scale, which can range between 0% and 100%.

    Second, I create a table to generate counts I’ll use to make the histogram. This table has four columns. The first of these is a column for the lower bound of a category. The second column is setting and counting based on an upper bound of a category. The third column has the X-axis labels or range labels I’m using for a histogram, and the fourth column has the counts of how many cases in my sample scored within the lower and upper bound.

    So, for example, my first column might be a lower bound, and in row two it has

    =COUNTIF(Scores :: Relationships,">=-0.005")

    in row three it has

    =COUNTIF(Scores :: Relationships,">=0.015")

    and then in the next row:

    =COUNTIF(Scores :: Relationships,">=0.025")

    and then

    =COUNTIF(Scores :: Relationships,">=0.035")

    and so forth up to the last row, which has

    =COUNTIF(Scores :: Relationships,">=0.995")

    And each of these rows gives me a count of all the instances in my sample where scores are over or equal to the lower threshold.

    (I’m making a histogram with 101 categories, going from 0% up to 100%. )

    In the second column I have an upper bound, and in row two this column has

    =COUNTIF(Scores :: Relationships,"<0.005")

    in row three it has

    =COUNTIF(Scores :: Relationships,"<0.015")

    and then

    =COUNTIF(Scores :: Relationships,"<0.025")

    and so forth up to the last row, which has

    =COUNTIF(Scores :: Relationships,"<1.005")

    In the third column, where I name the categories for my histogram, in the second row, I have

    0%

    in the third row of the third column, I have

    1%

    in the fourth row,

    2%

    and forth, down to the final row of the third column, where I have

    100%

    In the fourth column I have a remainder after subtracting the total count of my sample size from the sum of the numbers above-or-equal-to my lower bound and below my upper bound. The numbers that are between the lower bound and upper bound get counted twice, so when you sum the counts of those scores above-or-equal-to the lower bound and those lower than the upper bound you get a number higher than the total sample size when any cases were between those two bounds.

    So, in the second row of this fourth column I have

    =(A2+B2)-(COUNTIF(Scores :: Relationships,">-0.5"))

    and in the third row I have

    =(A3+B3)-(COUNTIF(Scores :: Relationships,">-0.5"))

    and forth to the last row, which is

    =(A102+B102)-(COUNTIF(Scores :: Relationships,">-0.5"))

    Third, To make the histogram, I copy the third and fourth columns from this second table, and then paste the values into a third table. The third table is inserted as a “headers” table, and the first column this becomes headers, and these start (in row 2) with 0%, and then in row three we have 1%, and so forth down to row 102, which is 100%. The second column, which has the pasted values from the fourth column of the second table in this sheet, has counts. So, it’s pretty easy to highlight this third table and create a chart that will have a nice histogram showing all possible percentages from 0% through 100% with the number of scores at each percentage value being a bar standing above the many values for which there are no scores.

    To create so many rows with nearly identical logical counts and functions I go back and forth between numbers and pages, using a table in Pages to paste in some columns of unchanging portions of the code while other columns have the part of the code that changes at some regular rate (up by 0.01 in the example I’ve given here). By converting table to text and using the find and replace-all command, it’s pretty easy to quickly generate 101 rows of code that create this sort of histogram. Of course, if you’re doing a normal histogram with just five or six or seven categories of scores, you can just as easily quickly type out the five or six or seven rows of formulas for counting cases above-or-equal-to lower bounds or below upper bounds in the first and second columns of that second table.

  4. 0

    Badunit

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Have you taken a look at the FREQUENCY function? It might make your spreadsheet a whole lot simpler. "The FREQUENCY function returns an array of how often data values occur within a range of interval values." Exactly what is needed for a histogram.

  5. 0

    Jerrold Green1

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Steven,

    I don't know how seriously you are "in stats", but If you are professionally into it, you should take a look at:
    http://www.jmp.com

    If you're a tinkerer, Google "statistics software for mac" and browse your options. Within Numbers, the function for calculating histogram data is FREQUENCY, and you will need to study the Help for usage tips.

    Jerry

  6. 0

    pts

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    This will work:

    1) create a column of numbers covering the range of values you want to assess for the histogram (e.g., if you have an exam with a maximum value of 100, create a column with the numbers 1-100 in order). You can do this by putting the number 1 in the first cell, the function =1+C1 in the second cell down, then drag (fill down) this value to the 100th cell.

    2) create a new column and paste the following function into the first cell:

    =INDEX(FREQUENCY((range of cells you want to histogram),(range of cells containing the list of values you created in step 1),(first cell of the range of cells containing the list of values you created in step 1)))

    Note that both:

    (range of cells you want to histogram), and

    (range of cells containing the list of values you created in step 1)

    need to be 'absolute row' for both ends of the range (for example: A$1:A$100). You can do this by entering the dollar signs by hand, or by clicking on the range in the function view (or whatever it is called) at the top of the spreadsheet and using the pulldown menu and selecting 'absolute row' for both values.

    3) Drag (fill down) the cell with the function you created in step 2 for as many rows as you created for step 1 (e.g., 100).

    4) If you desire, you can now make a histogram graph using this single row of histogram values that you created in step 3. I like to make the histogram bars to have a 'gap between bars' of 0%. This can be done by entering 0 into the 'gap between bars' section of the 'chart' tab of the 'chart inspector' of the inspector.

  7. 0

    smagavi

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Hi,

    Graphpad Prism is devoted to graphing and does what you want.

    Sanjay