Excel Hates Me

I am in a stats class as well... are you doing a box-and-whisker or something ?

https://support.microsoft.com/kb/155130

just a stupid histogram, but I can't get the bins to line up right, so i'm not getting the right data.

aaudetat, what do you need? I'm pretty good with excel, though I don't do graphs very often. Let me know and I'll try to help

It seems you've been helped. If still struggling, lemme now and I can try and help in around 2 - 3hs (when I finish work - yeah, I do work!).

Cheers. L.

Ok, I will post here rather than PMing.

Here's what I have:

A data set including the average gmat score for the top 25 schools, circa 1990, from what I can tell. (Stanford tops the list at 675 -- everyone take a moment to feel superior.)

I am supposed to make a histogram showing the frequency distributions with 9 classes: 595 to less than 605, 605 to less than 615.....675 to less than 685. My problem was that the histogram excel was giving me wasn't counting the frequencies correctly. There are 3 scores in the first class (or bin as excel says), but excel keeps giving me two.

Gogetter reminded me that there is online help that is better than the in-software stuff, so that was awesome. I realized that excel defines the bins a little differently than my book does - excel uses "less than or equal to 605," more than 605 and equal to or less than 615," etc, finishing with "more than or equal to 675"

That's ok with me; I've just jiggered the bins a bit and now they match up with the classes the book asks for. But for some reason, stinking excel is still cutting off the first data point - a gmat score of 597. So instead of getting 3 data points in bin #1, I get only 2. Instead of having 25 total data points, I've got 24. Why oh why?

The whole annoying thing is attached for your viewing pleasure.

I don't know...I've heard that poetry is really helpful in business school.

Ok, I figured out a work-around, but this is so stupid...

So for my first bin, there should be three data points, but I only get two.

So I choose the cell for that frequency bin and override it to say "3."

This fixes my chart. Beautiful, but stupid. I mean, really, there must be a way to get the software to do it.

Silly.

BTW - 7 downloads! I LOVE you people! (unless you're just doing it to see those old gmat scores....then I call you parasites on my sadness.)

Hah! Good idea. Download #8, here.

are you a parasite, or a recipient of my undying affection?

I don't get understand the problem. Why don't you just change 2 to 3 in your frequency counts and the chart will be correct. That will take care of the histogram.

if the problem is the frequency distribution, how did you get the bins?

For example I would have used a "countif" function to get the frequency distribution. so the formula =COUNTIF($column$row:$column$row,"<605"). If you do that with your scores, than you get 3.

Does that make sense?

Parasite, here.

I did change the 2 to a 3, but excel was supposed to come up with the 3 in the first place.

Here's what I did: the book came with CD that has all the data for each problem entered in excel. First, I sorted the data. According to the Excel Help, your next job is to enter your bins in a separate column in the same worksheet. I first entered the bins as my book told me (they want 9 of them, starting with 595-605), but realized that excel defines the bins differently, so then I tweaked the bins to get what you see in the GMATDAT 2 tab. After that, I used excel to create the frequency distribution table and the histogram chart. The histogram is supposed to be based on the frequency table, which excel generates based on the data set and the bins columns in the gmatdat2 worksheet.

I have never used "countif" but I understand the concept. Maybe it's better to use a "countif" formula than the bins? I mean, for a large data set you definitely don't want to be counting your frequencies by hand, so there must be a way to get excel to do it properly.

I will look for info on the countif formulas.

thanks!

What function are you using to do the bin calculation? Everything seems hardcoded to me, I dont see any formulas. Specifically what function are you using to come up with that frequency distribution table? I tried using FREQUENCY, but for some reason I couldn't come up with it cleanly. I was able to solve it using COUNTIF, though it takes a little math.

Anyway, if you want to use the countif function, I've attached a version using it.

The catch if you use the countif function is to subtract the previous counts from the logic... eg

=COUNTIF('GMAT.DAT (2)'!$B$3:$B$27,"<655")-E9-E8-E7-E6-E5

Anyway, here it is.

Double check the figures... im not 100% clear on what the bins are supposed to be (595 to 605 or 595 to 604?)

Much easier than countif, there's a macro built - into Excel for this stuff. Have you installed the Data Analysis tools? They do come with MS Excel but I recall you had to choose to have them installed at some point.

Anyway, check the attached document. After you've added the data analysis tools, choose Data analysis in the Tools menu and then input the same data I have in this example, and... voilá!

Cheers. L.

I don't really know how "bins" in excel work.

Another thing I use a lot are pivot tables. Pivots are very useful if you are dealing with large sets of data. They are under "data"; "pivot table and pivot chart report". You don't really need them for such a small data set as yours, but deffinitelly worth knowing about.

I dont have the data analysis stuff installed at work, so I have to hack it together

You're a hot tamale. I'm not so worried about exactly what the bins are supposed to be. The difference between the book and excel is that excel includes the upper limit, my book includes the lower. But whatever; either way it's cutting out the lowest data point. Anyway, creating the frequency table is definitely the problem. I followed these steps from the excel help:

Enter the data you want to use as input for the Histogram tool in one column. If you want, you can add a label in the first cell of this column.

In another column, enter the bin numbers you want to use for the analysis. The bin numbers need to be entered in ascending order.

Note: If you don't enter bin numbers, the Histogram tool automatically creates evenly distributed bin intervals using the minimum and maximum values in the input range as beginning and end points. However, these bins may not be useful— creating your own bin numbers is recommended.

More about bin numbers

Bin numbers represent the intervals you want the Histogram tool to use for measuring the input data in the data analysis. The Histogram tool counts the number of data points between the current bin number and the adjoining higher bin, if any. A number is counted in a particular bin if it is equal to or less than the bin number down to the last bin. All values below the first bin value are counted together, as are the values above the last bin value.

On the Tools menu, click Data Analysis.

In the Analysis Tools box, click Histogram, and then click OK.

Under Input in the Input Range box, enter the cell reference for the range of data you want to analyze.

Under Input in the Bin Range box, enter the cell reference to a range that contains an optional set of boundary values that define bin ranges.



It sounds like Counif is the way to go.