Find all School-related info fast with the new School-Specific MBA Forum

 It is currently 24 May 2016, 05:19

### GMAT Club Daily Prep

#### Thank you for using the timer - this advanced tool can estimate your performance and suggest more practice questions. We have subscribed you to Daily Prep Questions via email.

Customized
for You

we will pick new questions that match your level based on your Timer History

Track

every week, we’ll send you an estimated GMAT score based on your performance

Practice
Pays

we will pick new questions that match your level based on your Timer History

# Events & Promotions

###### Events & Promotions in June
Open Detailed Calendar

# Excel Hates Me

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

Kudos [?]: 200 [0], given: 2

### 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.

thanks
SVP
Joined: 31 Jul 2006
Posts: 2304
Schools: Darden
Followers: 43

Kudos [?]: 463 [0], given: 0

### Show Tags

05 Mar 2007, 11:18
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
Followers: 16

Kudos [?]: 200 [0], given: 2

### Show Tags

05 Mar 2007, 11:20
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
Followers: 1

Kudos [?]: 1 [0], given: 0

### Show Tags

05 Mar 2007, 11:20
I am in a stats class as well... are you doing a box-and-whisker or something ?

http://support.microsoft.com/kb/155130
SVP
Joined: 01 Nov 2006
Posts: 1855
Schools: The Duke MBA, Class of 2009
Followers: 16

Kudos [?]: 200 [0], given: 2

### Show Tags

05 Mar 2007, 11:21
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
Followers: 1

Kudos [?]: 1 [0], given: 0

### Show Tags

05 Mar 2007, 11:53
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
Followers: 10

Kudos [?]: 196 [0], given: 0

### Show Tags

05 Mar 2007, 12:00
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
Followers: 16

Kudos [?]: 200 [0], given: 2

### Show Tags

05 Mar 2007, 12:23
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.
Attachments

GMAT.XLS [19.5 KiB]

SVP
Joined: 31 Jul 2006
Posts: 2304
Schools: Darden
Followers: 43

Kudos [?]: 463 [0], given: 0

### Show Tags

05 Mar 2007, 12:42
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
Followers: 16

Kudos [?]: 200 [0], given: 2

### Show Tags

05 Mar 2007, 12:47
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.)
VP
Joined: 24 Sep 2006
Posts: 1359
Followers: 10

Kudos [?]: 196 [0], given: 0

### Show Tags

05 Mar 2007, 12:54
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.)

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

Kudos [?]: 200 [0], given: 2

### Show Tags

05 Mar 2007, 12:55
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.)

are you a parasite, or a recipient of my undying affection?
Senior Manager
Joined: 15 Jul 2006
Posts: 381
Followers: 1

Kudos [?]: 1 [0], given: 0

### Show Tags

05 Mar 2007, 12:58
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
Followers: 10

Kudos [?]: 196 [0], given: 0

### Show Tags

05 Mar 2007, 13:00
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.)

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
Followers: 16

Kudos [?]: 200 [0], given: 2

### Show Tags

05 Mar 2007, 13:06
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 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!
GMAT Club Legend
Affiliations: HHonors Diamond, BGS Honor Society
Joined: 05 Apr 2006
Posts: 5926
Schools: Chicago (Booth) - Class of 2009
GMAT 1: 730 Q45 V45
Followers: 294

Kudos [?]: 1912 [0], given: 7

### Show Tags

05 Mar 2007, 13:16
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?)
Attachments

file.xls [25.5 KiB]

VP
Joined: 24 Sep 2006
Posts: 1359
Followers: 10

Kudos [?]: 196 [0], given: 0

### Show Tags

05 Mar 2007, 13:18
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.
Attachments

Histogram.doc [43.5 KiB]

Senior Manager
Joined: 15 Jul 2006
Posts: 381
Followers: 1

Kudos [?]: 1 [0], given: 0

### Show Tags

05 Mar 2007, 13:21
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: 5926
Schools: Chicago (Booth) - Class of 2009
GMAT 1: 730 Q45 V45
Followers: 294

Kudos [?]: 1912 [0], given: 7

### Show Tags

05 Mar 2007, 13:26
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
Followers: 16

Kudos [?]: 200 [0], given: 2

### Show Tags

05 Mar 2007, 13:27
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")-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?)

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.

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 ]

Similar topics Replies Last post
Similar
Topics:
1 Mac vs Windows for b school? Notably Excel... 2 11 Jun 2013, 22:21
Excel 2010 - Any good books or courses? 3 27 Jun 2012, 06:55
shoot me now 10 27 Apr 2008, 16:06
Excel question 10 28 Mar 2008, 08:51
1 Make Me Care 28 22 May 2007, 18:27
Display posts from previous: Sort by