software

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,quot;quot;,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,quot;quot;,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 quot;Between A45-39 And
A45quot;, but can't.

Any help will be appreicated.

Regards.

CarlosWhy don't you just filter on A gt;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)quot;Carlosquot; gt; wrote in message
...
gt; My apologies for re-posting this to a new thread, need help from gurus out
gt; there.
gt;
gt; Say I want to get the STDEV of values in col B for 5 consecutive results
gt; starting from row 6 down to end and place it in col C.
gt;
gt; A B C
gt; 1 15
gt; 2 20
gt; 3 10
gt; 0 20
gt; 4 10
gt; 5 20 5.00 C6=STDEV(B5:B6,B1:B3)
gt; 0 15 C7= blank because A7=0
gt; 6 10 5.48 C8=STDEV(B8,B5:B6,B2:B3)
gt; 7 20 5.48 C9=STDEV(B8:B9,B5:B6,B3)
gt; 0 30 C10=blank because A10=0
gt; 8 25 6.71 C11=STDEV(B11,B8:B9,B5:B6)
gt; etc.
gt;
gt; I've come up with somewhat straightforward selection of non-zero rows in
gt; col
gt; A:
gt;
gt; C6=IF(A6=0,quot;quot;,STDEV(LOOKUP(A6-4,A$1:A6,B$1:B6),
gt; LOOKUP(A6-3,A$1:A6,B$1:B6),LOOKUP(A6-2,A$1:A6,B$1:B6),
gt; LOOKUP(A6-1,A$1:A6,B$1:B6),LOOKUP(A6,A$1:A6,B$1:B6)))
gt;
gt; But my real goal is to select the 40 consecutive results (for the first 40
gt; non-zero onward), i.e. my real case should be still long way down.
gt;
gt; So it would appear this way, say the first 40 appear in row 45:
gt;
gt; C45=IF(A45=0,quot;quot;,STDEV(LOOKUP(A45-39,A$1:A45:B$1:B45),......A45-38....down
gt; to.... LOOKUP(A45,A$1:A45:B$1:B45))
gt;
gt; But I know STDEV is limited to 30 numbers only, in my case it is 40.
gt;
gt; If I could simply place the criteria of something like quot;Between A45-39 And
gt; A45quot;, but can't.
gt;
gt; Any help will be appreicated.
gt;
gt; Regards.
gt;
gt; Carlos
gt;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
quot;Peo Sjoblomquot; wrote:

gt; Why don't you just filter on A gt;0 then copy the whole range to a new sheets,
gt; assume the filtered list's B column data starts in B2
gt;
gt; =STDEV(OFFSET($B$2,ROW(1:1)*40-40,,40,))
gt;
gt; copied down will work as
gt;
gt; =STDEV(B2:B41)
gt; =STDEV(B42:B81)
gt;
gt; and so on
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; (No private emails please)
gt;
gt;
gt; quot;Carlosquot; gt; wrote in message
gt; ...
gt; gt; My apologies for re-posting this to a new thread, need help from gurus out
gt; gt; there.
gt; gt;
gt; gt; Say I want to get the STDEV of values in col B for 5 consecutive results
gt; gt; starting from row 6 down to end and place it in col C.
gt; gt;
gt; gt; A B C
gt; gt; 1 15
gt; gt; 2 20
gt; gt; 3 10
gt; gt; 0 20
gt; gt; 4 10
gt; gt; 5 20 5.00 C6=STDEV(B5:B6,B1:B3)
gt; gt; 0 15 C7= blank because A7=0
gt; gt; 6 10 5.48 C8=STDEV(B8,B5:B6,B2:B3)
gt; gt; 7 20 5.48 C9=STDEV(B8:B9,B5:B6,B3)
gt; gt; 0 30 C10=blank because A10=0
gt; gt; 8 25 6.71 C11=STDEV(B11,B8:B9,B5:B6)
gt; gt; etc.
gt; gt;
gt; gt; I've come up with somewhat straightforward selection of non-zero rows in
gt; gt; col
gt; gt; A:
gt; gt;
gt; gt; C6=IF(A6=0,quot;quot;,STDEV(LOOKUP(A6-4,A$1:A6,B$1:B6),
gt; gt; LOOKUP(A6-3,A$1:A6,B$1:B6),LOOKUP(A6-2,A$1:A6,B$1:B6),
gt; gt; LOOKUP(A6-1,A$1:A6,B$1:B6),LOOKUP(A6,A$1:A6,B$1:B6)))
gt; gt;
gt; gt; But my real goal is to select the 40 consecutive results (for the first 40
gt; gt; non-zero onward), i.e. my real case should be still long way down.
gt; gt;
gt; gt; So it would appear this way, say the first 40 appear in row 45:
gt; gt;
gt; gt; C45=IF(A45=0,quot;quot;,STDEV(LOOKUP(A45-39,A$1:A45:B$1:B45),......A45-38....down
gt; gt; to.... LOOKUP(A45,A$1:A45:B$1:B45))
gt; gt;
gt; gt; But I know STDEV is limited to 30 numbers only, in my case it is 40.
gt; gt;
gt; gt; If I could simply place the criteria of something like quot;Between A45-39 And
gt; gt; A45quot;, but can't.
gt; gt;
gt; gt; Any help will be appreicated.
gt; gt;
gt; gt; Regards.
gt; gt;
gt; gt; Carlos
gt; gt;
gt;
gt;

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

Cheers.

Carlosquot;Carlosquot; wrote:

gt; Hi Peo,
gt;
gt; Appreciate your suggestion, Peo. You have the same idea with the other guy
gt; that had replied to me and unfortunately it is not applicable in my case. I
gt; still need to report the original data considering also that I would be
gt; working in about 20 worksheets with throusand of records in the future. Yes
gt; it would be simpler if zero's are filtered out so the formula is simple as
gt; =STDEV(B1:B40) and =STDEV(B2:B41) etc.
gt;
gt; Anyway, still hoping to come with the best solution when I come back to the
gt; office.
gt;
gt; Cheers! Happy New Year.
gt;
gt; Carlos
gt;
gt;
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt; gt; Why don't you just filter on A gt;0 then copy the whole range to a new sheets,
gt; gt; assume the filtered list's B column data starts in B2
gt; gt;
gt; gt; =STDEV(OFFSET($B$2,ROW(1:1)*40-40,,40,))
gt; gt;
gt; gt; copied down will work as
gt; gt;
gt; gt; =STDEV(B2:B41)
gt; gt; =STDEV(B42:B81)
gt; gt;
gt; gt; and so on
gt; gt;
gt; gt; --
gt; gt; Regards,
gt; gt;
gt; gt; Peo Sjoblom
gt; gt;
gt; gt; (No private emails please)
gt; gt;
gt; gt;
gt; gt; quot;Carlosquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; My apologies for re-posting this to a new thread, need help from gurus out
gt; gt; gt; there.
gt; gt; gt;
gt; gt; gt; Say I want to get the STDEV of values in col B for 5 consecutive results
gt; gt; gt; starting from row 6 down to end and place it in col C.
gt; gt; gt;
gt; gt; gt; A B C
gt; gt; gt; 1 15
gt; gt; gt; 2 20
gt; gt; gt; 3 10
gt; gt; gt; 0 20
gt; gt; gt; 4 10
gt; gt; gt; 5 20 5.00 C6=STDEV(B5:B6,B1:B3)
gt; gt; gt; 0 15 C7= blank because A7=0
gt; gt; gt; 6 10 5.48 C8=STDEV(B8,B5:B6,B2:B3)
gt; gt; gt; 7 20 5.48 C9=STDEV(B8:B9,B5:B6,B3)
gt; gt; gt; 0 30 C10=blank because A10=0
gt; gt; gt; 8 25 6.71 C11=STDEV(B11,B8:B9,B5:B6)
gt; gt; gt; etc.
gt; gt; gt;
gt; gt; gt; I've come up with somewhat straightforward selection of non-zero rows in
gt; gt; gt; col
gt; gt; gt; A:
gt; gt; gt;
gt; gt; gt; C6=IF(A6=0,quot;quot;,STDEV(LOOKUP(A6-4,A$1:A6,B$1:B6),
gt; gt; gt; LOOKUP(A6-3,A$1:A6,B$1:B6),LOOKUP(A6-2,A$1:A6,B$1:B6),
gt; gt; gt; LOOKUP(A6-1,A$1:A6,B$1:B6),LOOKUP(A6,A$1:A6,B$1:B6)))
gt; gt; gt;
gt; gt; gt; But my real goal is to select the 40 consecutive results (for the first 40
gt; gt; gt; non-zero onward), i.e. my real case should be still long way down.
gt; gt; gt;
gt; gt; gt; So it would appear this way, say the first 40 appear in row 45:
gt; gt; gt;
gt; gt; gt; C45=IF(A45=0,quot;quot;,STDEV(LOOKUP(A45-39,A$1:A45:B$1:B45),......A45-38....down
gt; gt; gt; to.... LOOKUP(A45,A$1:A45:B$1:B45))
gt; gt; gt;
gt; gt; gt; But I know STDEV is limited to 30 numbers only, in my case it is 40.
gt; gt; gt;
gt; gt; gt; If I could simply place the criteria of something like quot;Between A45-39 And
gt; gt; gt; A45quot;, but can't.
gt; gt; gt;
gt; gt; gt; Any help will be appreicated.
gt; gt; gt;
gt; gt; gt; Regards.
gt; gt; gt;
gt; gt; gt; Carlos
gt; gt; gt;
gt; gt;
gt; gt;