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 Your Progress

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

Not interested in getting valuable practice questions and articles delivered to your email? No problem, unsubscribe here.

Thank you for using the timer!
We noticed you are actually not timing your practice. Click the START button first next time you use the timer.
There are many benefits to timing your practice, including:

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.

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

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.

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

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Ã¡!

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.

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

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

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.