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
> >
> >
>
>
>