|
Re-post Standard Deviation
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 |
Re-post Standard Deviation
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 |
Re-post Standard Deviation
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 |
Re-post Standard Deviation
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 |
| All times are GMT +1. The time now is 08:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com