Re-post Standard Deviation

[Guest]

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

 

[Peo Sjoblom]

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)

 

[Guest]

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

 

[Guest]

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