Histograms in Excel 2008

Discussion in 'Mac Apps and Mac App Store' started by orbit, Sep 18, 2008.

  1. orbit macrumors member

    Joined:
    Sep 3, 2004
    #1
    Hi there,

    How would I go about creating a histogram of the number of students in each block of 10% scores (eg <10, 10-20, 20-30...... 90-100)

    If im given a list of marks like:
    although the actual list is much longer

    Code:
    30
    27.5
    10
    55
    62.5
    42.5
    70
    92.5
    61
    67.5
    67.5
    50
    87.5
    60
    25
    
    its just for a homework problem, but im still finding my way around excel 2008.

    Thanks
     
  2. nadyne macrumors 6502a

    Joined:
    Jan 25, 2004
    Location:
    Mountain View, CA USA
    #2
    Sorry to say, but Excel 2008 doesn't have support for histograms. :( You can do it with Excel 2004 with the analysis tools.

    An Excel guru could do something like a histogram using the REPT function, but that's honestly above my Excel capabilities.

    Regards,
    Nadyne.
     
  3. reclusivemonkey macrumors 6502

    reclusivemonkey

    Joined:
    Jun 2, 2008
    Location:
    Sowerby Bridge, West Yorkshire, UK
    #3
    Well, its certainly not pretty but you could do it with countif statements. Set up your ranges, then use

    =COUNTIF(range,"<10")

    then to work around COUNTIF weakness, your next one will have to be

    =COUNTIF(range,"<20")-SUM(PREVIOUSCELLS)

    where SUM(PREVIOUSCELLS) is the previous counts. You will have to keep adding those, as you can't do a "<10>20". Hope that makes sense. Then you can base your histogram on that. I am sure there is a more elegant solution but that will get your answer.
     
  4. MisterMe macrumors G4

    MisterMe

    Joined:
    Jul 17, 2002
    Location:
    USA
    #4
    I didn't know that Excel 2008 can't do histograms. However, Excel 2004 sucks at them. You need user-selectable bins. AFAIK, Excel 2004 provides no way for the user to select bin sizes. If you need to do a lot of graphs, then you should find a dedicated charting application. Excel may the the industry standard for spreadsheets, but it is strictly junior varsity in the charting game.
     
  5. orbit thread starter macrumors member

    Joined:
    Sep 3, 2004
    #5
    Ok I have had a bit of luck now.
    I have followed the histogram tutorial on this page: http://www.ncsu.edu/labwrite/res/gt/gt-bar-home.html

    But when I try to graph the data it plots the range and frequency as separate series. How would I make it so that the range is along the x-axis and the frequency is what is plotted.
     
  6. exegete77 macrumors 6502a

    Joined:
    Feb 12, 2008
    #6
    I don't have XL 2008, but I think that if you go into the data area, and change it using the Series tab instead of the Range tab it should adjust properly. But I am going from memory of a couple years ago.
     
  7. orbit thread starter macrumors member

    Joined:
    Sep 3, 2004
    #7
    sweet thanks, almost have it all worked out now.
    Except using the tutorial I posted above I can not get the x-axis values correct.
    They have to stay at 0, 10, 20 etc instead of being able to write them as 0 - 9, 10, 19 etc. which kind of sucks
     
  8. exegete77 macrumors 6502a

    Joined:
    Feb 12, 2008
    #8
    Not sure I understand: the "bin numbers" become the x-axis, not the original numbers. Perhaps you need to change the upper limits. It matters how Excel rounds the numbers.
     
  9. orbit thread starter macrumors member

    Joined:
    Sep 3, 2004
    #9
    ok, that tutorial is not working out right.
    Ive managed to get a copy of office 2004 for os X, anyone kind enough to tell me how to create a histogram in that?
    Ive been told its something to do with the analysis tools but cant even find them
     
  10. nadyne macrumors 6502a

    Joined:
    Jan 25, 2004
    Location:
    Mountain View, CA USA
    #10
    The Analysis Toolpak isn't part of the standard installation of Excel 2004. Go to the Tools menu, then select Add-ins. You'll need the Office 2004 discs to do it.

    As for how to create it once you've got the Analysis Toolpak installed, I stumbled across this site when I searched for "mac excel 2004 histogram": Graphing with Excel: bar graphs and histograms, which looks like it has good instructions.

    Regards,
    Nadyne.
     
  11. orbit thread starter macrumors member

    Joined:
    Sep 3, 2004
    #11
    thanks for the reply but thats the same tutorial I posted above
     
  12. wankeye macrumors newbie

    Joined:
    Sep 28, 2008
    #12
    Hey, I was googling for a solution to this problem and found this thread. After trying out a bunch of different things, it seems the thing to do is to get your frequency values, and then copy and paste only the data for your bins and your frequency values. Now, select all of your bin values and format them as text. Not done yet! Stupid excel still wants to read them as numbers, and it won't let you use them as x-values unless they are recognized as text, so I just put a space in front of every number.

    After that, select your data, click the stacked bar thing and it should spit out a bar graph with your bins as x-values, and your frequencies as frequencies. Now you just gotta fiddle with the spacing until you get it looking good. Now if only it were possible to make box-plots!

    I have no idea how the hell excel is so popular, it really is a tedious program and completely non-intuitive.
     
  13. nsbio macrumors 6502a

    nsbio

    Joined:
    Aug 8, 2006
    Location:
    NC
    #13
    Numbers 08 have a template ("Science lab") that is set up to do approximately the thing you are doing. It uses the COUNTIF function (subtraction of two countif conditions, to be exact, just as Reclusivemonkey had suggested). You can further alter this table by modifying the value to be subtracted to be an argument of, say, 1/10th of your result span (to make it 10 bins total) instead of it being a fixed value.

    Alas, there is no alternative. The same is also true for many inconveniences in our life, including, for example, traffic: that highway is jammed not because everyone enjoys using it, but because there is no alternative.

    Speaking about Excel, it is probably the only product of Microsoft's provenance that is actually useable and powerful. Too bad it practically requires a geek to take advantage of all its power. Even worse, the latest iteration of Excel, the 08, is a turd when compared with that from 04.
     
  14. echan00 macrumors newbie

    Joined:
    Apr 28, 2011
    #14
    You can also just download the template on http://www.excelhistograms.com

    I hated having to rework the excel formulas to create the bins every time i wanted to create new histograms.

    The template works like a charm :)
     
  15. jeremyschaar macrumors newbie

    Joined:
    Jun 2, 2011

Share This Page