Solved

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

Posted on 2010-08-16
3
2,601 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Introduction This article discusses the Chain of Responsibility pattern, explaining What it is;Why it is; andHow it is At the end of this article, I hope you will be able to describe the use and benefits of Chain of Responsibility.  Backgrou…
The Fluent Interface Design Pattern You can use the Fluent Interface (http://en.wikipedia.org/wiki/Fluent_interface) design pattern to make your PHP code easier to read and maintain.  "Fluent Interface" is an object-oriented design pattern that r…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now