Solved

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

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

One of the most frequent problems a "newbie" developer may encounter is having to deal with different data formats. One for all: THE DATE We, as humans, need to "see" a date and then interpret it (much of the times this is an automatic operation)…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

771 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

15 Experts available now in Live!

Get 1:1 Help Now