Re-post Standard Deviation

[Carlos]

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)


"Carlos" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>

[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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >