Hi,

Could someone please advise.

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?

Thanks in advance

Carlos

Try in say, C1: =STDEV(IF(A1:A40<0,B1:B40))
Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Carlos" wrote in message
...
Hi,

Could someone please advise.

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?

Thanks in advance

Carlos

Hi Max,

First of all thanks for your advice. I'm afraid I did not give more detail
of what my problem is.

To give you more idea of what my goal is all about, say I limit my criteria
to 5 consecutive instead of the 40 (to shorten my example below). Column C
to analyze the 5 consecutive results ignoring Column A with zero :
A B C
1 15
2 20
3 10
0 20
4 10
5 20 5.00 =STDEV(B5:B6,B1:B3)
0 15 blank because A7=0
6 10 5.48 =STDEV(B8,B5:B6,B2:B3)
7 20 5.48 =STDEV(B8:B9,B5:B6,B3)
0 30 blank because A10=0
8 25 6.71 =STDEV(B11,B8:B9,B5:B6)
etc.

I hope you get what I mean.

Any further help will be appreciated.

Thanks.

Carlos




"Max" wrote:

Try in say, C1: =STDEV(IF(A1:A40<0,B1:B40))
Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Carlos" wrote in message
...
Hi,

Could someone please advise.

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?

Thanks in advance

Carlos

Perhaps it would be simpler/quicker to just add a header row, then
autofilter on col A for rows < 0, and copy paste the filtered rows to
another sheet. Then over there, just put in say, C6: =STDEV(B2:B6) and copy
down
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Carlos" wrote in message
...
Hi Max,

First of all thanks for your advice. I'm afraid I did not give more detail
of what my problem is.

To give you more idea of what my goal is all about, say I limit my
criteria
to 5 consecutive instead of the 40 (to shorten my example below). Column
C
to analyze the 5 consecutive results ignoring Column A with zero :
A B C
1 15
2 20
3 10
0 20
4 10
5 20 5.00 =STDEV(B5:B6,B1:B3)
0 15 blank because A7=0
6 10 5.48 =STDEV(B8,B5:B6,B2:B3)
7 20 5.48 =STDEV(B8:B9,B5:B6,B3)
0 30 blank because A10=0
8 25 6.71 =STDEV(B11,B8:B9,B5:B6)
etc.

I hope you get what I mean.

Any further help will be appreciated.

Thanks.

Carlos

Max,

Appreciate your advice. I have thought of this before posting to the group.
I would have around 20 worksheets in one file and each worksheet would
contain thousands of records. I have found this approach time consuming.

Carlos

"Max" wrote:

Perhaps it would be simpler/quicker to just add a header row, then
autofilter on col A for rows < 0, and copy paste the filtered rows to
another sheet. Then over there, just put in say, C6: =STDEV(B2:B6) and copy
down
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Carlos" wrote in message
...
Hi Max,

First of all thanks for your advice. I'm afraid I did not give more detail
of what my problem is.

To give you more idea of what my goal is all about, say I limit my
criteria
to 5 consecutive instead of the 40 (to shorten my example below). Column
C
to analyze the 5 consecutive results ignoring Column A with zero :
A B C
1 15
2 20
3 10
0 20
4 10
5 20 5.00 =STDEV(B5:B6,B1:B3)
0 15 blank because A7=0
6 10 5.48 =STDEV(B8,B5:B6,B2:B3)
7 20 5.48 =STDEV(B8:B9,B5:B6,B3)
0 30 blank because A10=0
8 25 6.71 =STDEV(B11,B8:B9,B5:B6)
etc.

I hope you get what I mean.

Any further help will be appreciated.

Thanks.

Carlos

Apologies, I'm out of suggestions to offer you, Carlos.
Perhaps others may step in with something for you.
Hang around awhile. All the best.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Carlos" wrote in message
...
Max,

Appreciate your advice. I have thought of this before posting to the
group.
I would have around 20 worksheets in one file and each worksheet would
contain thousands of records. I have found this approach time consuming.

Carlos