Solved

How to overlay a Gaussian distribution curve over a frequency distribution (histogram) in Excel 2007?

Posted on 2010-08-16
3
2,638 Views
Last Modified: 2013-11-13
I have support groups and am capturing their monthly ticket counts by analyst in each group.  I am trying to show the relationship between the analysts average for a year against the groups' average for the year.  This gets updated every month.

I've been asked to plot the average for each analyst against the mean for the group and each standard deviation out to 4 from the mean.  So far, I have been able to create a histogram for a sample group and also a distribution curve for the mean and deviations.  But I can't marry the two types.  I have also  tried to create an X-Y Plot graph using the analyst means (not shown) but don't know how to overlay the curve.  I believe the point is to show which groups tend to have normal distrubutions and which have non-normal ones.

I believe what I'm looking for is similar to this:
http://www.graphpad.com/help/prism5/prism5help.html?graphing_frequency_distribution_histograms_with_prism.htm

Attached is a sample group with charts so far.  This group clearly not normally distrubuted.  I've researched EE and other Excel sites and this seems to be a fairly common question but I haven't been able to apply any of the specific solutions.
GroupSample-081610.xlsx
0
Comment
Question by:shadowbreeze
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
calacuccia earned 500 total points
ID: 33465303
Hello

Attached you'll find your workbook with an example of how to combine a generated normal distribution with the calculated histogram, on a X-Y chart, where the normal distribution is plotted as a solid smooth curve and the histogram is repolotted (and rescaled to fit well) as points with Y-error bars going down to the x-axis and maximally increased in size to make it look more or less as a line graph.

This is in my opinion the only way to achieve what you're looking for, I think it resolves your issue.

Good luck & HTH
GroupSample-081610-1-.xlsx
0
 

Author Closing Comment

by:shadowbreeze
ID: 33468657
The good news is the solution answers my question exactly.  The bad news is I think I need to review the results and ask a different question.  Thanks.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33469189
I'm sorry to hear about the bad news, but let's see your next question, I'll try to be of help again.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Software development teams often use in-memory caches to improve performance. They want to speed up access to, or reduce load on, a backing store (database, file system, etc.) by keeping some or all of the data in memory.   You should implement a …
Introduction A frequently used term in Object-Oriented design is "SOLID" which is a mnemonic acronym that covers five principles of OO design.  These principles do not stand alone; there is interplay among them.  And they are not laws, merely princ…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question