EXCEL: Formula to Calculate the Standard Deviation for Subsets of a Range
Michael.Lumley
Let's say I have a range containing 200 values.
I'd like a formula that calculates the standard deviation of the top X of those values, where X is a value in a cell. For example, the standard deviation of the top 50 cells.
I'd also like a formula that, given a value in the range, calculates the standard deviation of the next X values in the range. For example, for value 15, the standard devation of values 15 - 15+x.
I feel confident that I can get the second formula on my own, given the first, but I'm blanking on how to do a standard deviation in this context. Average was a lot easier, using SUMIF and LARGE.
Tom Sharpe
OK so the first part would look something like this:-
=STDEV(LARGE(A2:A11,ROW(INDIRECT("1:"&D2))))
where A2:A11 contains some test data and D2 contains how many, starting from the largest, you want to use in your calculation for which I am indebted to this
It's an array formula so has to be entered with Ctrl Shift Enter
If you had a certain value in your data (specified in D3) and wanted the stdev of that value and the next n-1 values in descending order (n specified in D4), the formula would look like this:-
=STDEV(LARGE(A2:A11,ROW(INDIRECT(RANK(D3,A1:A10)&":"&(RANK(D3,A1:A10)+D4-1)))))
If on the other hand you wanted to get the stdev of values between certain limits (specified in D5 and D6) you could use the original idea of setting non-used values to blanks using the following:-
=STDEV(IF((A2:A11>=D5)*(A2:A11<=D6),A2:A11,""))
So this example gives the same answer (stdev of 5 highest numbers) three different ways.
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: Moodle dynamically load css file
- Next: PHP stops executing after specific amount of recursion loops
Related
Related Related
- 1
How to calculate average and standard deviation from the same range in excel
- 2
Standard Deviation IF Excel {} Formula Issues
- 3
Standard Deviation IF Excel {} Formula Issues
- 4
How to calculate the standard deviation of subsets of data down a column in R
- 5
Calculate Standard Deviation in Oracle
- 6
Calculate the standard deviation of a gaussian
- 7
Calculate a moving Standard Deviation
- 8
Calculating Standard Deviation with If in Excel
- 9
How to calculate standard deviation in R
- 10
How to calculate standard deviation in R
- 11
Calculate mean and standard deviation in Wireshark
- 12
Summation in EXCEL for calculation of standard deviation
- 13
Conditional standard deviation and standard errors in Excel
- 14
Group a dataframe and calculate normalised standard deviation by group?
- 15
How to calculate standard deviation per row?
- 16
Pandas dataframe groupby to calculate population standard deviation
- 17
Calculate the 3rd standard deviation for an array
- 18
Calculate standard deviation of certain columns in data frame
- 19
Calculate the standard deviation of grouped data in a Spark DataFrame
- 20
lapply - dividing columns and calculate standard deviation
- 21
How to calculate the standard deviation for each row?
- 22
Calculate rolling standard deviation using ave()
- 23
How to calculate the standard deviation for each row?
- 24
SAS: Calculate standard deviation based on rolling window
- 25
Group a dataframe and calculate normalised standard deviation by group?
- 26
C++ calculate average and standard deviation
- 27
Python - calculate weighted rolling standard deviation
- 28
R:how to calculate poblational standard deviation and mean
- 29
Spark Calculate Standard deviation row wise
Comments