[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

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

Posted on 2010-08-16
Medium Priority
2,796 Views
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:

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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 17

Accepted Solution

calacuccia earned 2000 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

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

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

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Suggested Courses
Course of the Month13 days, 2 hours left to enroll