?
Solved

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

Posted on 2010-08-16
3
Medium Priority
?
2,746 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
[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
  • Learn & ask questions
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
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

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

777 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