Standard Deviation

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" <Carlos@discussions.microsoft.com> wrote in message
news:03706C20-0ADD-403F-B84E-38D907C35E71@microsoft.com...
> 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" <Carlos@discussions.microsoft.com> wrote in message
> news:03706C20-0ADD-403F-B84E-38D907C35E71@microsoft.com...
> > 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" <Carlos@discussions.microsoft.com> wrote in message
news:965E1550-A650-4AFF-A149-2376AC9D6D1C@microsoft.com...
> 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" <Carlos@discussions.microsoft.com> wrote in message
> news:965E1550-A650-4AFF-A149-2376AC9D6D1C@microsoft.com...
> > 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" <Carlos@discussions.microsoft.com> wrote in message
news:D93BDDAD-B78A-40E0-B9AC-50A4DF118550@microsoft.com...
> 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