How to get standard deviation of certain range of numbers ?

[babai97]

suppose I have a list of numbers starting from 0.1 to 378 and it contains almost 1179 numbers . I want to get the standard deviation of the numbers upto 13 . How can I get this without filtering and using functions ? I am attaching a file which have this series of values. Please help me with a function that can be used to get the average of certain range i.e upto 13 .

[kvsrinivasamurthy]

Formula is

Please Login or Register  to view this content.

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.

[bebo021999]

Check with this:

Please Login or Register  to view this content.

[babai97]

But I want to get the standard deviation value of the numbers which are less than or equal to 13 in the excel sheet

[babai97]

If I filter the values which are equal to ar less than 13 , and apply STDEVA then it comes 2.96 which are not maching with th evalue obtained from above equations

[bebo021999]

So you wish "<=13" is appllied for numbers in column A, right?

What if:
A1=1
A2=2
A3=5
A4=14
A5=6
Without the criteria "<=13",the deviation should be: A5:A2-A4:A1 = {-8;9;3;1}
With the critea "<=13", what the desired out put should be?

[NeedForExcel]

Basically you want standard deviation only of Numbers that are less than 13 and the Standard Dev should ignore any number grater than or equal to 13? Is that right?

[NeedForExcel]

Here -

Please Login or Register  to view this content.

This returns 2.96

Commit using Ctrl+Shift+Enter

The Function STDEV.P is for Population and STDEV.S is for Sample.. Use as required..

However, the Aggregate function will only work in Excel 2010 & further.. Wont work in Excel 2007

Cheers!

[babai97]

YES @msexcelathome

[NeedForExcel]

Did it work as required?