+ Reply to Thread
Results 1 to 2 of 2

Bell Curve/Standard Deviation/Graph

  1. #1
    Registered User
    Join Date
    04-03-2020
    Location
    NYC
    MS-Off Ver
    365 Pro Plus
    Posts
    72

    Question Bell Curve/Standard Deviation/Graph

    Hello,

    So I have a data set containing 5 company departments with each of their total spending per month for a whole year.

    I am trying to set up a bell curve graph that shows the standard deviations/distribution of data so that it can easily be seen which departments have spending that is outside of, let's say, 3 standard deviations.

    I just need some help setting up the chart and with ensuring that my calculations are correct.

    I believe that I need to:
    1) Calculate the average spending for the whole year for each department
    2) Calculate the total average of the average department spends
    3) Calculate the standard deviation of all the average spends for the departments
    4) set up a Norm.Dist to calculate the distributions of each of the departments spending
    5) set up a bell curve chart that shows the distribution of data and highlights which departments are outside of the normal deviations (e.g. spending way too much or way too little)

    I believe I am on the right track but just need some help with the last several steps.

    I've attached my excel sheet here which may better show the problem.

    Any help would be appreciated! Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,819

    Re: Bell Curve/Standard Deviation/Graph

    Up through step 3 looks fine.
    4) looks fine -- except the probability density function PDF (4th argument is FALSE) will show the "bell" shaped curve. The cumulative distribution function CDF (4th argument is TRUE) is more of an S shaped curve that goes from 0 to 1. You can also calculate how many standard deviations each is away from the average with something like =(x-xaverage)/s -> =(C14-$F$14)/$F$19. If the end goal is to simply decide who is way above or way below the average, this might be enough without the need to build the chart.
    5) You can easily create a chart with what you already have. Simply select C14:C18,J14:J18 -> Insert chart -> scatter chart.
    5a) To add a "bell curve" to this, you will need to calculate several data points along the bell curve in the spreadsheet, then add those data as an additional data series. Enter a reasonable range of values (I entered 200 to 600 every 20 in C24:C44 and =NORM.DIST(C24,$F$14,$F$19,FALSE) [or use TRUE if you decide you prefer the CDF]. Then add this range as a new data series.
    5b) Other chart formatting as desired.

    Is that what you are trying to do?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Bell curve graph - HELP
    By lemonap618 in forum Excel General
    Replies: 1
    Last Post: 09-09-2019, 11:37 AM
  2. Bell Curve and Standard Deviation
    By zmster2033 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-19-2015, 05:27 PM
  3. Bell Curve Graph
    By Moelesjones89 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-11-2014, 11:56 AM
  4. How to create a bell curve / normal distribution graph
    By anstasija in forum Excel General
    Replies: 5
    Last Post: 04-17-2011, 10:21 PM
  5. Creating a bell chart for standard deviation
    By Allthingshr in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-26-2011, 08:15 PM
  6. How do I create a bell graph for standard deviation?
    By Lucien in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 06-23-2006, 12:00 AM
  7. How do I create a Bell Curve Graph in Excel
    By fratton in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-17-2005, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1