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 RepliesI 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
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: ....
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.......................
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!
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%
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.
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 RelatedSay 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.
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"))}
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?
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
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))))))
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%
Just like sumif and averageif, is there a standarddeivationif?
View 1 Replies View RelatedIs there anyway to do a standard dev w/o the max + min in a range of numbers?
View 8 Replies View RelatedI 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 RelatedI 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.
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.
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] .........
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.
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.
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%).
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.
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.
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?
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?
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 RelatedI 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
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.