My apologies for re-posting this to a new thread, need help from gurus out
there.

Say I want to get the STDEV of values in col B for 5 consecutive results
starting from row 6 down to end and place it in col C.

A B C
1 15
2 20
3 10
0 20
4 10
5 20 5.00 C6=STDEV(B5:B6,B1:B3)
0 15 C7= blank because A7=0
6 10 5.48 C8=STDEV(B8,B5:B6,B2:B3)
7 20 5.48 C9=STDEV(B8:B9,B5:B6,B3)
0 30 C10=blank because A10=0
8 25 6.71 C11=STDEV(B11,B8:B9,B5:B6)
etc.

I've come up with somewhat straightforward selection of non-zero rows in col
A:

C6=IF(A6=0,"",STDEV(LOOKUP(A6-4,A$1:A6,B$1:B6),
LOOKUP(A6-3,A$1:A6,B$1:B6),LOOKUP(A6-2,A$1:A6,B$1:B6),
LOOKUP(A6-1,A$1:A6,B$1:B6),LOOKUP(A6,A$1:A6,B$1:B6)))

But my real goal is to select the 40 consecutive results (for the first 40
non-zero onward), i.e. my real case should be still long way down.

So it would appear this way, say the first 40 appear in row 45:

C45=IF(A45=0,"",STDEV(LOOKUP(A45-39,A$1:A45:B$1:B45),......A45-38....down
to.... LOOKUP(A45,A$1:A45:B$1:B45))

But I know STDEV is limited to 30 numbers only, in my case it is 40.

If I could simply place the criteria of something like "Between A45-39 And
A45", but can't.

Any help will be appreicated.

Regards.

Carlos

Why don't you just filter on A 0 then copy the whole range to a new sheets,
assume the filtered list's B column data starts in B2

=STDEV(OFFSET($B$2,ROW(1:1)*40-40,,40,))

copied down will work as

=STDEV(B2:B41)
=STDEV(B42:B81)

and so on

--
Regards,

Peo Sjoblom

(No private emails please)


"Carlos" wrote in message
...
My apologies for re-posting this to a new thread, need help from gurus out
there.

Say I want to get the STDEV of values in col B for 5 consecutive results
starting from row 6 down to end and place it in col C.

A B C
1 15
2 20
3 10
0 20
4 10
5 20 5.00 C6=STDEV(B5:B6,B1:B3)
0 15 C7= blank because A7=0
6 10 5.48 C8=STDEV(B8,B5:B6,B2:B3)
7 20 5.48 C9=STDEV(B8:B9,B5:B6,B3)
0 30 C10=blank because A10=0
8 25 6.71 C11=STDEV(B11,B8:B9,B5:B6)
etc.

I've come up with somewhat straightforward selection of non-zero rows in
col
A:

C6=IF(A6=0,"",STDEV(LOOKUP(A6-4,A$1:A6,B$1:B6),
LOOKUP(A6-3,A$1:A6,B$1:B6),LOOKUP(A6-2,A$1:A6,B$1:B6),
LOOKUP(A6-1,A$1:A6,B$1:B6),LOOKUP(A6,A$1:A6,B$1:B6)))

But my real goal is to select the 40 consecutive results (for the first 40
non-zero onward), i.e. my real case should be still long way down.

So it would appear this way, say the first 40 appear in row 45:

C45=IF(A45=0,"",STDEV(LOOKUP(A45-39,A$1:A45:B$1:B45),......A45-38....down
to.... LOOKUP(A45,A$1:A45:B$1:B45))

But I know STDEV is limited to 30 numbers only, in my case it is 40.

If I could simply place the criteria of something like "Between A45-39 And
A45", but can't.

Any help will be appreicated.

Regards.

Carlos

Hi Peo,

Appreciate your suggestion, Peo. You have the same idea with the other guy
that had replied to me and unfortunately it is not applicable in my case. I
still need to report the original data considering also that I would be
working in about 20 worksheets with throusand of records in the future. Yes
it would be simpler if zero's are filtered out so the formula is simple as
=STDEV(B1:B40) and =STDEV(B2:B41) etc.

Anyway, still hoping to come with the best solution when I come back to the
office.

Cheers! Happy New Year.

Carlos



"Peo Sjoblom" wrote:

Why don't you just filter on A 0 then copy the whole range to a new sheets,
assume the filtered list's B column data starts in B2

=STDEV(OFFSET($B$2,ROW(1:1)*40-40,,40,))

copied down will work as

=STDEV(B2:B41)
=STDEV(B42:B81)

and so on

--
Regards,

Peo Sjoblom

(No private emails please)


"Carlos" wrote in message
...
My apologies for re-posting this to a new thread, need help from gurus out
there.

Say I want to get the STDEV of values in col B for 5 consecutive results
starting from row 6 down to end and place it in col C.

A B C
1 15
2 20
3 10
0 20
4 10
5 20 5.00 C6=STDEV(B5:B6,B1:B3)
0 15 C7= blank because A7=0
6 10 5.48 C8=STDEV(B8,B5:B6,B2:B3)
7 20 5.48 C9=STDEV(B8:B9,B5:B6,B3)
0 30 C10=blank because A10=0
8 25 6.71 C11=STDEV(B11,B8:B9,B5:B6)
etc.

I've come up with somewhat straightforward selection of non-zero rows in
col
A:

C6=IF(A6=0,"",STDEV(LOOKUP(A6-4,A$1:A6,B$1:B6),
LOOKUP(A6-3,A$1:A6,B$1:B6),LOOKUP(A6-2,A$1:A6,B$1:B6),
LOOKUP(A6-1,A$1:A6,B$1:B6),LOOKUP(A6,A$1:A6,B$1:B6)))

But my real goal is to select the 40 consecutive results (for the first 40
non-zero onward), i.e. my real case should be still long way down.

So it would appear this way, say the first 40 appear in row 45:

C45=IF(A45=0,"",STDEV(LOOKUP(A45-39,A$1:A45:B$1:B45),......A45-38....down
to.... LOOKUP(A45,A$1:A45:B$1:B45))

But I know STDEV is limited to 30 numbers only, in my case it is 40.

If I could simply place the criteria of something like "Between A45-39 And
A45", but can't.

Any help will be appreicated.

Regards.

Carlos

I got it !!
=IF(A1<40,"",STDEV(IF((A$1:A1=A1-39)*(A$1:A1<=A1),B$1:B1)))
Hope this work by the time I reach several thousands of records.

Cheers.

Carlos


"Carlos" wrote:

Hi Peo,

Appreciate your suggestion, Peo. You have the same idea with the other guy
that had replied to me and unfortunately it is not applicable in my case. I
still need to report the original data considering also that I would be
working in about 20 worksheets with throusand of records in the future. Yes
it would be simpler if zero's are filtered out so the formula is simple as
=STDEV(B1:B40) and =STDEV(B2:B41) etc.

Anyway, still hoping to come with the best solution when I come back to the
office.

Cheers! Happy New Year.

Carlos



"Peo Sjoblom" wrote:

Why don't you just filter on A 0 then copy the whole range to a new sheets,
assume the filtered list's B column data starts in B2

=STDEV(OFFSET($B$2,ROW(1:1)*40-40,,40,))

copied down will work as

=STDEV(B2:B41)
=STDEV(B42:B81)

and so on

--
Regards,

Peo Sjoblom

(No private emails please)


"Carlos" wrote in message
...
My apologies for re-posting this to a new thread, need help from gurus out
there.

Say I want to get the STDEV of values in col B for 5 consecutive results
starting from row 6 down to end and place it in col C.

A B C
1 15
2 20
3 10
0 20
4 10
5 20 5.00 C6=STDEV(B5:B6,B1:B3)
0 15 C7= blank because A7=0
6 10 5.48 C8=STDEV(B8,B5:B6,B2:B3)
7 20 5.48 C9=STDEV(B8:B9,B5:B6,B3)
0 30 C10=blank because A10=0
8 25 6.71 C11=STDEV(B11,B8:B9,B5:B6)
etc.

I've come up with somewhat straightforward selection of non-zero rows in
col
A:

C6=IF(A6=0,"",STDEV(LOOKUP(A6-4,A$1:A6,B$1:B6),
LOOKUP(A6-3,A$1:A6,B$1:B6),LOOKUP(A6-2,A$1:A6,B$1:B6),
LOOKUP(A6-1,A$1:A6,B$1:B6),LOOKUP(A6,A$1:A6,B$1:B6)))

But my real goal is to select the 40 consecutive results (for the first 40
non-zero onward), i.e. my real case should be still long way down.

So it would appear this way, say the first 40 appear in row 45:

C45=IF(A45=0,"",STDEV(LOOKUP(A45-39,A$1:A45:B$1:B45),......A45-38....down
to.... LOOKUP(A45,A$1:A45:B$1:B45))

But I know STDEV is limited to 30 numbers only, in my case it is 40.

If I could simply place the criteria of something like "Between A45-39 And
A45", but can't.

Any help will be appreicated.

Regards.

Carlos