Histograms in Excel: Analysis of Lab Report Grading

[Pengwuino]

I want to have an analysis of my lab report grading for my students and I figured I could make a histogram. I had been manually doing =COUNTIF()'s before but I learned you could install a histogram package just now! However, it seems to be giving me conflicting results that have to do with how the BIN sizes work.

Here's what I have: A bunch of report grades from 0-20 and I occasionally give students half integer grades such as 15.5 or 18.5 or whatever. I have a list of grades, 0 to 20 in integer steps. I do a COUNTIF() and count up the integer guys and the half integer guys. For example, for the 15 score, i count up 15s and 15.5s. Then I plot those both as 15s. However, when I use the actual histogram function, I get numbers that aren't exactly what I have by doing COUNTIF()s. I used the 0-20 list as my bin array. Apparently that must be causing the problem. I assumed, and as far as any site that I read told me, that if they go as 1,2,3,4,5 etc, that it would count how many scores are 1-1.999,2-2.999,3-3.999, etc but that doesn't seem to be exactly how it works. Can someone explain how Excel does this?

Thanks!

 

[berkeman]

I'm not sure what the problem is, but could you fix it by just adding a column with the scores truncated (to force the 15.5s into the 15 bin, as you say you want to do anyway)? Then you can run your histogram on the new column instead...

 

[Andre]

PW,

Not sure exactly either what you are looking for but if this excel sheet vaguely resembles that, maybe we should discuss matrix funxions with the ctlr-shift-enter trick.

Andre

 

[Pengwuino]

@Berkeman, That would probably take too much time for what I want to do. I also want to figure out exactly how it works for future reference. My students grades are in basically a 25x12 grid... 25ish students, 12 labs. I would have to truncate everything... which I would assume be by hand which defeats the purpose of being able to just toss up a quick histogram to figure out what the breakdown looks like.

@Andre I'm not sure what happened but that spreadsheet you sent me got screwed up. Everything seems to have ASELECTUSSEN(4,100) as the entries...

 

[Andre]

@Andre I'm not sure what happened but that spreadsheet you sent me got screwed up. Everything seems to have ASELECTUSSEN(4,100) as the entries...

Sorry about that, it's probably a mismatch in function names with the Dutch Excel version I happen to have on this laptop.

Anyway the new one has the function replaced by numbers, to demonstate how to make a histogram with the matrix frequency function.

Prepare it by entering your data series (column b) and the bins (column d) in which to divide them.

Then select the cells in a column for the function frequency or in Dutch interval, most convient directly next to the bins hence e4:e13 here.

Enter the data range B4:B103 and bin range D4:D13 and wave the magic wand using <ctrl> + <shift> + <enter> (CSE) to complete the array function.