Author 
Message 
SVP
Joined: 01 Nov 2006
Posts: 1855
Schools: The Duke MBA, Class of 2009

Excel Hates Me [#permalink]
Show Tags
05 Mar 2007, 11:01
Could someone take a few minutes and help me with Excel? I am taking an online stats class. While it's not required, I would like to make the various graphs using excel rather than by hand.
But excel is being mean to me  I know I am doing some small stupid thing wrong, but I can't figure it out. Pillsbury Doughboy to whoever wants to give me a hand.
I eagerly await your reply....
thanks



SVP
Joined: 31 Jul 2006
Posts: 2292
Schools: Darden

I don't know how to use excel. I am a lawyer and a poet.



SVP
Joined: 01 Nov 2006
Posts: 1855
Schools: The Duke MBA, Class of 2009

Well, darlin', you'd best get off your ass and learn. I hear we're all going to this place called business school in the fall....



Manager
Joined: 08 Feb 2007
Posts: 82

I am in a stats class as well... are you doing a boxandwhisker or something ?
http://support.microsoft.com/kb/155130



SVP
Joined: 01 Nov 2006
Posts: 1855
Schools: The Duke MBA, Class of 2009

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



Senior Manager
Joined: 15 Jul 2006
Posts: 381

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



VP
Joined: 24 Sep 2006
Posts: 1359

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.



SVP
Joined: 01 Nov 2006
Posts: 1855
Schools: The Duke MBA, Class of 2009

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 insoftware 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.
Attachments
GMAT.XLS [19.5 KiB]
Downloaded 86 times



SVP
Joined: 31 Jul 2006
Posts: 2292
Schools: Darden

aaudetat wrote: Well, darlin', you'd best get off your ass and learn. I hear we're all going to this place called business school in the fall....
I don't know...I've heard that poetry is really helpful in business school.



SVP
Joined: 01 Nov 2006
Posts: 1855
Schools: The Duke MBA, Class of 2009

Ok, I figured out a workaround, 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.)



VP
Joined: 24 Sep 2006
Posts: 1359

aaudetat wrote: 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.



SVP
Joined: 01 Nov 2006
Posts: 1855
Schools: The Duke MBA, Class of 2009

lepium wrote: aaudetat wrote: 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?



Senior Manager
Joined: 15 Jul 2006
Posts: 381

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?



VP
Joined: 24 Sep 2006
Posts: 1359

aaudetat wrote: lepium wrote: aaudetat wrote: 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?
Parasite, here.



SVP
Joined: 01 Nov 2006
Posts: 1855
Schools: The Duke MBA, Class of 2009

EconGirl wrote: 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?
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 595605), 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!



GMAT Club Legend
Affiliations: HHonors Diamond, BGS Honor Society
Joined: 05 Apr 2006
Posts: 5917
Schools: Chicago (Booth)  Class of 2009
WE: Business Development (Consumer Products)

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")E9E8E7E6E5
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?)
Attachments
file.xls [25.5 KiB]
Downloaded 70 times



VP
Joined: 24 Sep 2006
Posts: 1359

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.



Senior Manager
Joined: 15 Jul 2006
Posts: 381

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.



GMAT Club Legend
Affiliations: HHonors Diamond, BGS Honor Society
Joined: 05 Apr 2006
Posts: 5917
Schools: Chicago (Booth)  Class of 2009
WE: Business Development (Consumer Products)

lepium wrote: 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 dont have the data analysis stuff installed at work, so I have to hack it together



SVP
Joined: 01 Nov 2006
Posts: 1855
Schools: The Duke MBA, Class of 2009

rhyme wrote: 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")E9E8E7E6E5
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?)
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.







Go to page
1 2
Next
[ 23 posts ]



