Array Formula - St Deviation

Mar 2, 2009

I have a list of 187 countries and I would like to find a way to do this with an array and not by hand. Attached here is an excel with a sample; I would like to compute the standard deviation of the variable for the period 1980-2008 for each country separately. Is there a way to do this without me having to select by hand the range corresponding to each country in the st dev formula?

View 5 Replies


ADVERTISEMENT

Calculate Standard Deviation Of Array

Mar 14, 2014

I am trying to calculate the Standard Deviation of an array. However, to populate the array, I have the following:

Count Number 1 Count Number 2 CountNumber 3
1 100 2 200 3 300

For example, this above means that there are 1 100's, 2 200's, 3 300's. I would ideally like to populate an array that is {100,200,200,300,300,300} based on those 6 columns that provide a count and a number. Then, I would like to calculate the standard deviation based on those (in this case it is 74.5).

array.jpg

View 1 Replies View Related

Create Standard Deviation From Array

Feb 1, 2008

I am fairly new to VBA, i require some help creating a Standard Deviation UDF using arrays, i have made a userform that allows a user to select anywhere between 1 to 30 companies (A1: AD1) and its corresponding stockprices (A1:A60) in a sheet called companydata, using the following sub, my problem is that i am unsure how to create a function using the info the user selects. I have also created a returns function (below) but this is vba is saying that there is a out of range error, Will anyone be able to help please.

Below is the code i am using to allow for my sub: ....

View 8 Replies View Related

Average Standard Deviation Of Array Within Matrix

Mar 7, 2008

I have tried to find information on this subject, but it seems diifficult to find exactly what I am looking for. I have provided a test example of the operation that I would like to acheive. The first example (Method 1) works well enough but would require defining more detailed functions beyond the scope of average. This would probably be ok, but more importantly, I would like to source particular arrays in this manner for other tasks beyond averages and even standard deviation and am not quite sure how this can be done. In an attempt to describe the situation, I have provided a second example (Method 2). In this example, I am trying to take the average of the jth column array to create an array of averages. I am not sure if I am incorrectly setting up the matrix, if the functions don't perform this way in the matrix, or if this is simply not possible.

Sub test()
a = 5
b = 5
Redim X(1 To a, 1 To b) As Double
'Assumes there is a matrix of values [a x b] drawn from worksheet
For j = 1 To b
For i = 1 To a
X(i, j) = Cells(i, j)
Next
Next

'----METHOD 1---------------------------------------------
'I know this way works using average as an example, but...
Redim AverageX(1 To a, 1 To b) As Double.......................

View 4 Replies View Related

Array Formulas For Geometric Standard Deviation And Sharpe

Oct 17, 2005

Since I got such fantastic help with the last quesiton let me try one that
I've been beating my head against the wall on for two weeks now.

I'm calculating CAGR (Compound Annual Growth Rate) of an investment using
the following formula:

=((GEOMEAN(IF(Work!$A9:$A236<B2,Work!$DS$9:$DS$236))^12-1)*100)

A Column: Dates
D Column: Monthly returns in multiplier format

I want to do the same thing as this with GSD using this basic formula --
that is, only do the returns based on the IF statement:

=(EXP(STDEV(LN(Work!B9:B236)))^SQRT(12)-1)*100

And Sharpe with this formula:

=(AVERAGE(B49:B276)/STDEV(B49:B276))*SQRT(12)

Any help you can offer will certainly save me a ton of pain -- head's
beginning to hurt!

View 6 Replies View Related

Formula For 5 Years Average Deviation?

Jan 12, 2013

Looking for a formula to calculate 5 yr avg deviation.

year # of event

2007 59
2008 63
2009 53
2010 42
2011 46

with this set of numbers I can figure out the formula for 5 yr avg but can't figure out how to arrive @ the -18.3%.

where 5 yr avg is 52.6
where 5 yr avg deviation is -18.3%

View 5 Replies View Related

Formula For Three Sigma Rule Of Standard Deviation

Sep 20, 2009

I want to find the correct formula to find answers using excel for the folliowing:

1. >3Sds
2. 3SDs<>2SDs
3. 2SDs<>1SD
4. <1SDs

This question uses 3 sigma rule (see attached worksheet) and the formula needs to be re-arranged to meet new criteria. So, we need to consider both sides of nornmal distributiion (ND) curve. eg. when we say >3SD it means it should count all valuee falling in the rage "Mean(Average)+3SD AND Mean-3SD" i.e two extreme ends of the ND cureve. I have inserted a normal distribution diagram and colour coded the ranges so that it will be easy to identify. Please refer comments inserted in K7to N7, cells that contain formula that need to be reset.

View 2 Replies View Related

Standard Deviation Formula That Ignores 0 Values

Sep 10, 2006

I am trying to come up with a STDEVA formula that will only count values greater than 0 in a data set. Is this possible?

View 2 Replies View Related

Converting 3x10 Array To A 1X30 Array To Run A Match Formula

Apr 7, 2009

Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.

Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?

Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.

And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.

I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.

View 6 Replies View Related

Index Match Array New Formula Not Array?

Nov 6, 2013

Is there anyway to recreate this formula w/o it being an array ?

{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}

View 5 Replies View Related

Concatenate An Array (using Array Formula)

May 18, 2007

I just wanted to concatenate a few cells using an array formula like this:

{=SUM(IF(B2:J2="";0;B2:J2))}

Well, this does not work. I have no idea why it fails. Any solution to this without scripting?

View 9 Replies View Related

Using Array Variable Instead Of Array Formula

Jun 30, 2006

i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it
in writing so that i accurately describe my problem. attached is my example

View 4 Replies View Related

"You Cannot Change Part Of An Array" When Editing Array Formula

Aug 25, 2006

I am trying to copy or edit a cell thats has the following formula (see below), I keep getting this message "You cannot change part of an Array!"

=If(ROW($A2:$A8)-ROW(A2)+1>COUNT(W2:W8),"",INDEX($A:$A,SMALL(W2:W8,ROW(INDIRECT("1:"&ROWS(A2:A8))))))

View 5 Replies View Related

Deviation From 0?

Sep 26, 2007

I'm looking for the proper way of determining the success of a forecast over time.

Forecast ranges from -25% to +25%.

I don't want an average, as it would return a 0% deviation over 2 periods.
I don't want a standard deviation as it would be deviation from an eventual mean greater than 0.

I want the result of the above to say that my average deviation is 25 basis points. Meaning I'm either high or low by 25 points or that I'm "usually" off by +/- X%

View 9 Replies View Related

Standard Deviation IF?

Mar 24, 2014

Just like sumif and averageif, is there a standarddeivationif?

View 1 Replies View Related

Standard Deviation W/O Max+Min

Jun 22, 2009

Is there anyway to do a standard dev w/o the max + min in a range of numbers?

View 8 Replies View Related

Standard Deviation - Set Of Data

Oct 11, 2013

I have a set of data and need to get Stdev 1, Stdev 2 and Stdev 3 but i honestly not sure what are those. I have attached the file : stdev.xlsx

View 4 Replies View Related

Moving Standard Deviation

Dec 4, 2008

I am trying to learn VBA and started reading Wallenbach's book and I thought the best way to get up to speed fast was to work on some real problems.

So this is what I am trying to do.

I have a list of about 1000 stock price returns in the following format

jan 1 2008 .033
jan 2 2008 .01
jan 3 2008 -.002
jan 4 2008 -.0033
jan 5 2008 .04

What I want to do is calculate in VBA a moving 3 day standard deviation of returns. So I want the first calculation to include jan 1 - jan 3 and place the answer in the cell to the right of jan 3 return. Then move down 1 row and calculate the standard deviation using jan 2 - jan4 and place the answer in the cell to the right of jan 4 return...and so on until the last cell.

View 4 Replies View Related

Standard Deviation Of Database

Jun 20, 2007

i'd like to calculate the standard deviation of different items all in the same database

If this would be the database:
a 1
a 2
b 9
a 6
b 5
b 3
c 2
c 5
a 7

Than this would be the result:
item a 2.55
item b 2.49
item c 1.50

I succeeded in doing this via a pivot table but i want to create a calculation as the result should be part of other data in another sheet.

View 6 Replies View Related

Standard Deviation Of Given Date

Dec 10, 2011

The spreadsheet below shows the Date (Column A), Percentage (Column B), and Standard Deviation (Column C). What I would like is a macro that finds the standard deviation for all percentages of a given date, and for the output to be placed in the first cell of Column C of that given date. If a cell in Column C is not the first for a given date I would like for it to be blank.

Date Percentage Stan Dev (Output) 8/19/09 26.75% 0.77% 8/19/09 26.79%
8/19/09 28.14%
8/19/09 26.57%
8/19/09 26.40%
8/19/09 25.54%
8/19/09 26.61%
9/16/09 36.54% 3.04% 9/16/09 37.27%

[Code] .........

View 3 Replies View Related

Graphing Standard Deviation

Mar 19, 2009

I was wondering what the best way to create a standard deviation graph was? I have a single row of numbers and need to make a stadard deviation graph.

I read somewhere that the analysis toolpak would be useful for this but having installed it i'm not really sure what to do next.

View 7 Replies View Related

Data With Fixed Mean And Standard Deviation

Feb 10, 2009

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.

View 2 Replies View Related

Calculating AVERAGE DEVIATION From A SPECIFIED POINT?

Jul 29, 2009

I have a set of % score values (e.g. 88%, 94%, 82%, 67%)

I would like to know if there is a relatively simple formula that can be used to calculate the AVERAGE DEVIATION from 100%, as opposed to the AVERAGE DEVIATION from the mean, which is what the excel function =AVEDEV will return.

E.g. The Average Deviation from the mean for the the above set of values is 8.25%, whereas the Average Deviation from 100% is 17.25%

I can create a complex manual formula which sums absolute values of the variation from 100% of each score, and then divides by the count total of the number of scores, however this is flawed for several reasons, including the fact it is very time consuming when used across a large set of scores, and it is corrupted when a non score exists in a cell (some of the 'scores' may be '-', in which case they should be ignored and not included in the calculation of the average deviation from 100% by treating as a 0% score (and a deviation of 100% from 100%).

View 14 Replies View Related

Standard Deviation Of Dynamic Range..

Aug 10, 2009

I have a dynamic range that I need to be able to calculate the standard deviation for. The dynamic range sometimes has blank cells and #N/A cells which I do not want included in the calculation. However, there are sometimes cells with the number 0 in it (instead of blank) which I do want to include. I have come up with an array formula, but the standard deviation it calculates is not correct. The formula is:

=STDEV(IF(ISNUMBER(A3:A20000)*ISNUMBER(A3:A20000),1,0)). *This is an array formula calculated by pressing CTRL+SHIFT+ ENTER. The number it calculates is 0.43, however, this is incorrect. If you just calculate the standard deviation of the actual data points in excel (cells A138:A5055 in this case) : STDEV(A138:A5055), it returns 0.17. This is the correct standard deviation.

View 2 Replies View Related

Standard Deviation And Nested If Statement

Oct 15, 2009

I have a sheet of data in which Column A contains a list of sales agents names and Column B contains their results against their KPIs. All the data is terribly skewed due to new metrics so I have been tasked with attributing 'ranking band' to their performance, i.e. very low, low, medium, high, very high.

I have calculated the average and standard deviation for the list of data, but now I need to calculate which ranges will fall under which ranking band, e.g. very low is less -18%, low is greater than 18% but less than 80%, etc.

I also need to come up with a nested if statement next to each of the sales agents so it returns what ranking brand they are on based on their performance. Sample sheet attached. EDIT - attachment on post #4.

View 5 Replies View Related

Standard Deviation Ignoring N/A With Max+Min Removed

Nov 17, 2009

So this forum has helped me in the past develop a standard deviation that removes max and min from the data set.

=STDEV(LARGE(E5:AB5,ROW(INDIRECT("2:"&COUNT(E5:AB5)-1))))

Would if be possible to adjust this formula to ignore N/A's?

View 6 Replies View Related

Standard Deviation Based On Criteria

Dec 30, 2005

I want to get the standard deviation based on criteria. It looks like this.
Criteria is 40 consecutive values ignoring 0 in Column A.
A B
1 50
2 20
3 10
0 20
4 10
5 20
....
....
nth

I use manual selection (e.g. =STDEV(B1:B3, B5:B6, etc...) that covers the 40
consecutive rows. B4 here is zero so I did not select it.

My rows is getting bigger and bigger, how could I formulate this?

View 10 Replies View Related

Calculating Robust Mean And Standard Deviation

Aug 2, 2013

I am trying to calculate a Robust mean and a robust standard deviation.For Example how to calculate robust mean and robust standard deviation on "Calcium" on the document here: [URL] .......

View 3 Replies View Related

Macro/UDF To Calculate Standard Deviation Of % Changes

Jul 8, 2002

I have a seris/column of numerical data for which I want to work out percentage differences and then calculate the standard deviation of the percentage differences for all values

For example in column A, I have

row 1 10
row 2 15
row 3 17
row 4 10

I want to work out % difference between row 1 and row 2 (which is 50%), row 2 and row 3 etc and the get the standard deviation of thoose % changes.


I know how to do this on the spreadsheet using formulas but I want to do it using a macro so that the macro returns the standard deviation of the percentage changes because I do not want to have any data showing on the spreadsheet other than the raw data.

kkknie kindly provided the following UDF to work out the the average of the percentage changes but is there code that will work out the standard deviation instead?

I thought maybe excel's built-in standard deviation function (stdev)could be incorporated somewhere into the code but I'm not sure how!

Function AvgPct(rngIn As Range)
Dim dblErrAccum As Double
Dim intErrCount As Long
Dim dblLastVal As Double
Dim r as range
dblErrAccum = 0
dblLastVal = 0
intErrCount = 0

For Each r In rngIn
intErrCount = intErrCount + 1
If intErrCount > 1 Then
'dblErrAccum = dblErrAccum + (r.Value - dblLastVal) / dblLastVal
dblErrAccum = dblErrAccum + Abs((r.Value - dblLastVal)) / dblLastVal
End If
dblLastVal = r.Value
Next

AvgPct = dblErrAccum / (intErrCount -1)

End Function

View 9 Replies View Related

Calculate The Standard Deviation Of A Whole Population

Nov 17, 2006

I want to calculate the standard deviation of a whole population in my database (total population: 36,458)

I used the STDEV equation however, I went into help and it says that the equation calculates STDEV based on a sample of 30.

I don't want to calculate a standard deviation based on a sample. I want a standard deviation based on the whole population. Is it still okay to use the equation?

The reason I want to calculate the standard deviation is so that I can use that number to calculate the sample size needed for my population so I can create a histogram.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved