Free Microsoft Excel 2013 Quick Reference

# plot histogram & indicate means and std deviation

I would like to know the best way to sort the data and specigy the "bin" to
plot a histogram in Excel.
also, i would like to know whether i can indicate the means and standard
deviation on the plot. also, i would like to know if there is a way to
include those values (means 1x std deviation and 2xstd deviation) in the data
series so that they appear on the plot.

## Related Results

### Plotting means and standard deviations I already have

I have a chart that I just cannot figure out how to graph.
It is some survey results and the mean and standard deviation of each
question.
I want the horizontal axis to list 1.0, 1.5, 2.0, 2.5, 3.0, 3.5, 4.0, 4.5,
and 5.0.
I want the vertical axis to list the question topic: eg, a, b, c, d, e, f,
g, h, and i.
For each question, I want to show the mean answer and the standard deviation
on a bar, with a point for the mean and the endpoints being the +/- standard
deviation.
Examples of the data:
Question a - Mean = 3.6875 Std. Dev. = .59387
Question b - Mean = 3.8958 Std. Dev. = .67810
Question c - Mean = 3.1875 Std. Dev. = .65730
etc.

How can I set this up to create a chart like what I need, where I basically
have a bar that stretches plus or minus the standard deviation, with the mean
in the center?
Thanks,
Kim

### How do I plot mean and standard deviation in excel

I want to take a data series and plot the mean and standard deviation on a
bar graph. How do I do this in excel?

### Mean and Standard Deviation

Is there a formula I can use to calculate the mean and standard deviation of a selection of data? I have tried a few different ones and they don't seem to be working correctly.

### Simulate values for a given Mean and Standard deviation

I want to simulate values for a given mean and standard deviation. wat
is the procedure to do this? is there any free excels addins available
to do this.
For example, My portfolio return(mean) is 12% and Risk(SD) is 18% . Now
I want to simulate values for next 30-40 years Returns. How to do this?
is there any free add-ins that wud generate these yearly returns if we
provide the population mean and standard deviation.

### Plot mean and sd

Hello,

i would like to plot mean and standard deviation like this:

http://www.nzfooddaily.co.nz/Portals...ience-blog.jpg

I already have a list of mean and SDs, but i really dont have a clue on how to show them on a graph.

Thanks for any hint

Cheers!

### Histrogramms: Calculating average and standard deviation

Hello group,

from an external tool I get as output a histogram with my measurment
values and their occurences (number of samples) during the whole
measurement.

I am now interested in the average and std. deviation of the
measurement values.

Do you know a function?

Thanks and best regards,
Jens

### VBA output mean and sd required

Dear all,

Im not sure if you read my previous thread, but I really need your help as I am a beginner to VBA. I have created some VBA code which calculates some 'IndexSim' values and Payoff values. Below is the part of the code that performs the calculation (all variables and functions have been defined properly):
```
VB:
Redim IndexSim(1 To SampleSz) As Double
Redim Payoff(1 To SampleSz) As Double

'Do the simulations.
For Cnt = 1 To SampleSz
IndexSim(Cnt) = gBmProcess(Kt, r, q, Vol, TMat - TNow)

If IndexSim(Cnt) >= IndexBetValue Then
Payoff(Cnt) = Range("D18").Value
Else
Payoff(Cnt) = Range("D19").Value
End If

Next Cnt

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
Please could someone advise me what code to write to output the mean and standard deviation of the IndexSim values.

Many thanks,

Suz

### VBA Code Mean and SD

Hello all,

I am new to VBA and am stuck on trying to compute the mean and standard deviation for the following output:
```
VB:
RunMCSimButton_Click()

'Sub created so that once the macro button is pushed, it outputs a specified number
'of FTSE 100 values and the equivalent binary payoffs.

Dim Kt As Double
Dim q As Double
Dim r As Double
Dim IndexBetValue As Double
Dim SampleSz As Long

Dim IndexSim() As Double
Dim Cnt As Long

Dim Payoff() As Double

'All the relevant variables have been defined.

'Read the parameter values from the required cells.
With Me.Range("d4")
Kt = .Cells(1, 1).Value
q = .Cells(2, 1).Value
r = .Cells(7, 1).Value

IndexBetValue = .Cells(10, 1).Value

SampleSz = .Cells(12, 1).Value

End With

' Create storage for FTSE100 and binary payoff simulations.
Redim IndexSim(1 To SampleSz) As Double
Redim Payoff(1 To SampleSz) As Double

'Do the simulations.
For Cnt = 1 To SampleSz
IndexSim(Cnt) = gBmProcess(Kt, r, q)

If IndexSim(Cnt) >= IndexBetValue Then
Payoff(Cnt) = Range("D18").Value
Else
Payoff(Cnt) = Range("D19").Value
End If

Next Cnt

'Output the index values.
With Range("C23")
For Cnt = 1 To SampleSz
.Cells(Cnt, 1).Value = IndexSim(Cnt)
Next Cnt
End With

'Output the binary payoffs in accordance to the FTSE 100 values.
With Range("C23")
For Cnt = 1 To SampleSz
.Cells(Cnt, 2).Value = Payoff(Cnt)
Next Cnt
End With

End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
As you can see from the code, by pressing the macro button a list of FTSE 100 values and binary payoffs are produced and outputted into the spreadsheet. What I need help on is writing a piece of code that, also on the click of the macro, will output the mean and standard deviation of these values in specified cells, e.g. C10 mean and C11 SD.

Many thanks,

Suz

### Calculating a standard deviation

Hi
I have a range of values in a sheet 1 named Yrly Pr.
The data are in the range B3 to K3.
How do i use VBA to calculate the mean and std deviation, for this range, and plug it into Sheet2.
However, the Std Deviation must be calculated so that it is 2 std dev above and 2 Std Dev below the mean.

Can this be done using functions?

thanx
Suju

### VBA module/function to generate random numbers with specified mean and SD

Hi all,

As basis for a school assignment I need to generate 20 random numbers that
have a prespecified mean and standard deviation ( I don't believe they have
to be normally distrubted).

Can anyone help?

The output would preferably be to Worksheets("Sheet1").Range("A1:A20")
though an array would also work.

Thanks.

### Calculating Std Deviation and Asset Weighted deviation of accounts grouped by class

I am no expert at all in Access all I can do partially wel is run queries, this i what I have to do I have two tables that derive from queries.

The first one gives the whole accounts fro the firm and a check mark on the composite classification they belong too. and then for every month of each year the account existed in teh firm it calculates the gross profit.

Something like this

Acct # Composite 1 Composite 2 Composite 3 200202 200203... 200607
231 1 1.2% -1% 3%
256 0 1 0 0 2% 1.6%
288 1 0 0 0 0 1%
291 0 1 0 3% 2.2% -1% 213 0 0 1 2% .2% 1.4%

And the second table we obtain The accounts and their beggining market value for those same periods

Acct# 200202 200203..................200607
231 \$300,000 \$350,000 645000
256 0 100000 150000
288 0 0 50000
291 600000 595000 550000
213 1500000 1450000 1320000

I made up the numbers as an example since I cant provide the actual data.

Now I need to create a query in which it will calculate the standard deviation and the asset weighted std deviation for Each composite, like If I specify in the query to show these results for composite 1 It will show the accounts belonging to that composite and the std dev and Asset Wiegth Std for each month of the years:I have attached the example bt I solved this manually in excell. And I need to figure out a way to do it in access through a query.

These are the formulas:

Std Deviation= ((Sum(Return%of Acct #-MEan %tot)/n))^1/2

n=number of accounts that had a return on that month.

Mean %Tot= Sum the returns of the accounts for that month/n

Asset Weighted Std Dev= Sum( BegMarket Value of Account#*(Return of thr account#))/Sum of all the beginning Mkt values of the accounts of that month.

THANKYOUUU soo much I know this is probably too complicated or tooo basic but if you can give me some pointers it woudl be greatle appreciated

THANKS!!!!

### How to obtain both std dev and std error mean in each y error bar

I am making column charts in Excel. Does anyone know if it is possible
in Excel to setup the y error bar for each series so that it provides
multiple values? I would like dash marks, one for the std deviation and
one for std error mean, on the same vertical line. I've seen it done
elsewhere, and would be suprised if Excel doesn't have this feature,
but can't seem to find it.

Also, when I calculate the std error mean or the std deviation in
Excel, the returned value equals the size of the error in one direction
(ex. positive or negative) right? So if I use this value for a y error
bar, I should put it in twice, once in the positive box and once in the
negative box, which graphically doubles the total size of my error. Is
this correct, or is the value itself already accounting for error in
both directions, so that I should halve the value before putting it in
each box?

Kerry

### How to calculate Mean, Median, Mode and standard deviation in group data?

Hello,

I am trying to create a worksheet capable of calculating the turnaround processing time for emails in a 14 days time frame.

I am wondering if it is possible to create a dynamic excel worksheet which can calculate Mean, Median, Mode and standard deviation within a range of data as Column B listed below changes every forthnightly.

Sample data below:

Column A Column B
Number of days Email
O – 1 600
2 - 3 700
4 - 5 100
6 - 7 50
7 - 14 30

- First row: 600 emails took a day to complete
- Last row: 30 emails took between 7 - 14 days to complete

1. To find the average processing time
2. To find the median processing time in days
3. What is the standard deviation (in days)

Would greatly appreciate if someone could help.

I apologise if I was unable to articulate the whole scenario clearly.

Many Thanks!

### Can you plot individual numbers on a bell curve in excel, and how.

I have created a bell curve in excel with the mean and std. dev. and now need
to plot the actual values as they fall along the curve. Does anyone know how
I can plot these???

### Forumula for Excel Bell Curve and Histogram

H everyone,

I'm trying to draw a histogram and a bell curve with some data. I have the categories and frequency of each category, however I still lack a formula that takes into account the mean and standard deviation for the chart.
Please refer to the enclosed attachment.

Best Regards

Bernard

### Data with fixed mean and standard deviation

Hi

I'm a beginner using Excel to do more advanced stuff than inserting chart and using simple formulas.

Now i need simulate data in 10 cells with a fixed standard deviation and a fixed mean

Example: Simulating the laptime for a runner where the mean should be 50 and the standard deviation 5,

I tried typing
=50+5*RAND()*(-1^(RANDBETWEEN(1;2)))
That gives me a mean close to 50 which is good but th standard deviation is no where near 5.
Anyone who knows how to solve this problem?

Thanks
Arvid

### How do I plot mean and error bars in Excel?

I am using Excel 2003 and cannot figure out how to plot means and standard
error bars, where I have data arranged like this:

3 5
5 9
6 4
7 3
Mean Mean

### Plotting Mean and SD on the same Line graph

Hello,

I have a requirement in which I need to plot the average and the Standard Deviation (Error bar) in the same graph. I am using Excel 2010.

I am making a line graph. I am able to plot average easily but with standard deviation, I am facing problems.

For every point on the line graph, I have different standard deviation and when I select "Custom" on the Standard Deviation Options and I specify the range of SD values for each point, I get the SD lines of the same size and they are so huge that it crosses the chart itself. I am sure I can't select "Fixed", "Percentage" as each of the SD value is different corresponding to each point (average value) on the line graph.

Any help on this will be highly appreciated.

Thanks!

EDIT: Sample File Attached - As you see in the chart, the line graph is plotted based on the "Average" values, I need to plot SD on those points as well.

### Excel Graphing and stuff

Hey i'm very new to excel and i've been told it is a powerful if not essential tool in the statistics world (which i will be majoring in when i go off to college in a month).

My dad gave me a problem about graphing a normal distribution or a histogram or somethin.

Part 1:

He gave me the following conditions.

N=500 (n=sample size)
Mu=50 (mu=mean)
sigma=20 (sigma=standard deviation)

Now with that info, he wants me to draw a normal distribution graph or histogram.

Part 2:

Once the graph is completed, he wants me to truncate the graph takin the beginning 20, so pretty much cutting a piece off of the left side of the normal bell-shaped graph.

Then with that new graph, he wants me to estimate the mean and standard deviation with excel.

Can this be done? I'd really appreciate any help! thx a lot.

### Mean of standard deviations across columns?

I'm looking for an (array) formula that will take the mean of standard deviations across several columns given the value of another column. For example, I would have

A B C D
2 4 2 1
2 2 2 1
4 2 1 0

I am looking for a formula that will take the mean of the standard deviations of A B and C IN EACH ROW given that column D contains value 1.

(Note that I am not trying to take the standard deviations of all the values in columns A, B and C where the value in column D is 2. I'm trying to take an average of the standard deviations. I have not succeeded in finding an array formula that will do this.

Thanks again for everyone's help on previous problems!

### How to plot pdf using Excel 2010

Hi,

I want to plot PDF for a variable called WOI as follows.

Year Avg WOI
2003 0.272947792
2005 0.396405549
2004 0.73213832
2002 0.939609286
2000 0.954349704
1999 1.543786496
2001 1.56797841
1998 1.837185048

I found mean and Std dev. as 1.030550076 and 0.570980062 for the data set.

Thanks.

Kasun

### VBA Function to calculate Mean and Standard Deviation

In 3 rows (B1:B3) and in 5151 columns (till column GPD) I got alle the combinations which sum up to 1. (Thanx for the help in an other thread).

The first return I calculated in the following way.
=MMULT(TRANSPOSE(B1:B3);H12:H14)-1

The Standard Deviation is calculated in this way.
=SQRT(MMULT(MMULT(TRANSPOSE(B1:B3);C24:E26);B1:B3))

See attached excel sheet.

It would be very helpful if a VBA script can be written which automatically calculates the 5151 returns and the 5151 standard deviation.

### Normal Distribution Curve Overlay

Hi! How do I plot a normal distribution curve on a histogram on Excel 2007? I have the mean and standard deviation. The values for my normal distribution curve are as follows:
Bubble size range No of bubbles
9.5-10.0mm 2
10.1 -10.5mm 7
10.6-11.0mm 3
11.1- 11.5mm 9
11.6-12.0mm 7
12.1-12.5mm 7
12.6-13.0mm 2
13.1-13.5mm 11
Thanks!

### Column charts and std deviation

I'm trying to make some pretty straightforward column charts and then add error bars for standard deviation on the charts, but I can't get it to do the standard deviation for each individual column of data. I can only get all of the columns to use the same standard deviation (whichever one i choose as my custom value).

Anyways I am able to set each bar to have a unique standard deviation error bar?

Using Excel 2007 btw.

Thanks