Excel bell curve assistance

I am trying to draw a bell curve of some data, but for the life of me can't get it to work..

Basically I have alist of employees and their salaries, and I need to plot them on a bell curve to display their distribution.

In the first group I am looking at I have just 9 employyes, but I need to plot this for other groups, so the data could grow as much as 100 people in one group.

I have the following data in place:

stddev: 2.67
mean: £9.82
Min: £6.83
Max: £14.51
No of data points: 9 (£6.83, £7.60, £7.78, £7.78, £9.07, £9.13, £12.14, £13.51, £14.51)


I have then worked out a data table, showing my distributions, but....

IT WON'T BLOODY WORK

Can anyone please assist? Thanks as ever y'all

Bog off JipO.

You only come on here when you've an Excel/damp/tablet problem!

P.S. How're you doing in the FCCUK F1 league??

Thanks for the warm welcome...

I seem to be very close to winning the f1 league, assuming its lowest points win again this year...

Oh Jim you really are hopeless, last time it took you ten years to forget, this time you've forgotten in just over a year

With your 9 points, I get Std Dev = 2.833 using Excel?

Anyways, in cells A1:A9 I put your data values, in A11 I put MIN, A12 is MAX, A13 is =STDDEV(A1:A9) and A14 is =AVERAGE(A1:A9)

In B1 put =NORMDIST(A1, $A$14, $A$13, FALSE) and copy down to B2:B9

In C8 I put =A14 (the average, or mean)

In C7 I put =C8-1 and copy it up to C1:C6, similarly in C8 put =C7+1 and copy down to C9:C15

The copy B1 into cells D1:D15

Now highlight C1:D15 and on the ribbon click on Insert then click on Scatter and choose Scatter with Smooth Lines. This creates your "bell curve"

Then to show the actual data point, right click on the chart and choose Select Data... and then press Add. For X values you want A1:A9 and for Y values B1:B9 then press OK. You'll get a wonky line, but we'll fix that, so right click and select Format Data Series. For Line Color select No line, then close.

Select the chart again and then from the ribbon (Chart Tools) click on Layout and click on Error Bars then select More Error Bar options at the bottom. Click on Series 2, OK, then for Vertical Error Bars click on Minus and No Cap, select Percentage and enter 100. You'll now have some horizontal bars to get rid of, so right click on one of them and select Format Error Bars... and this time enter 0 in the fixed value box and press close.

You can now remove the legend, or fatten up the error bars, add labels or whatever you want to make it look pretty. Is that what you want Jim?

Thanks Dave, am just on phone at moment, but as soon as I get home I,ll give that a bash.

It seems similar to what I was trying with some added bits, so hopefully you've fixed my stupidity (or maybe that fix may take longer! )

More likely to be a partial workaround than a fix if you're anything like this Jim...

I get close Dave, but I don't get the cigar

The curve just looks and is wrong!

A late night tinkling the keys looms!

I have one kicking around somewhere that I'll dig up tomorrow to see if it works with your data. Will be later on though as I have a date with a cement mixer during the day!

A bit more tricky in Excel 2013 (pull up Error Bars by clicking on the + to the right of the chart) but here's what I got

Maybe if you put up a sketch of what you want Jim, we can do it for you? (Apart from the stupidity fix )

Wishy, i'll swap your cement mixer for this any day of the week!

So I think I may be asking and trying to build slighty the wrong thing then, as I do get the graph that Dave has shown, but thats not exactly what I need! Allow me to try and explain my needs!

I think this is to do with percentiles, or at least in my mind it is!

• The far left of the curve, the minimum should be £6.83, or a P0
• The far right of the curve, the maximum should be £14.51, or a P100
• The peak would be the average, or a P50.
  

The full list of distribution being:

Basically, I have told the client, min and max salaries, which were the P10 & P90 and the P50, and they have requested to see the distribution of the rest of the salaries within those points!

Does that make sense, and is it possible in Excel?

I've just dug out the one that I did a couple of years ago and the solution looks like it uses the same NORMDIST() formulae as Dave's above. I'll have a little play to see what it looks like anyway.


I won't be swapping the cement mixing for anything, that was fun that was.

This sort of thing but as Dave mentions above, a picture (of what you're expecting) is worth a thousand words. The red line on mine is going to be the same as DaveG's as it uses the same base formula, NORMDIST().


One thing I'm thinking, looking at the type of data, is that a gamma distribution (Excel has a function for this GAMMADIST() ) is going to be more suitable than a standard bell curve (which I'm interpreting as a normal distribution).

Re-reading your last post again, is it a cumulative frequency graph that you are after, something like this with an x-axis from 6 to 15 and a y-axis from 0 to 100)?