Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

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.


Post your answer or comment

comments powered by Disqus
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

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?

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.

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.

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!

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

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

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.
Please Help,

Many thanks,

Suz

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

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.

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!!!!

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?

Thanks for your help,

Kerry

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!

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???

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.
Can someone please help and solve this problem for me please? Thank you very much.

Best Regards

Bernard

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

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

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.

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.

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!

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.
Can anyone please help me with this?

Thanks.

Kasun

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.

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!

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


No luck finding an answer? You could always try Google.