software

Hi,

Could someone please advise.

I want to get the standard deviation based on criteria. It looks like this.
Criteria is 40 consecutive values ignoring 0 in Column A.
A B
1 50
2 20
3 10
0 20
4 10
5 20
....
....
nth

I use manual selection (e.g. =STDEV(B1:B3, B5:B6, etc...) that covers the 40
consecutive rows. B4 here is zero so I did not select it.

My rows is getting bigger and bigger, how could I formulate this?

Thanks in advance

Carlos
Try in say, C1: =STDEV(IF(A1:A40lt;gt;0,B1:B40))
Array-enter the formula, i.e. press CTRL SHIFT ENTER
instead of just pressing ENTER
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
quot;Carlosquot; gt; wrote in message
...
gt; Hi,
gt;
gt; Could someone please advise.
gt;
gt; I want to get the standard deviation based on criteria. It looks like
this.
gt; Criteria is 40 consecutive values ignoring 0 in Column A.
gt; A B
gt; 1 50
gt; 2 20
gt; 3 10
gt; 0 20
gt; 4 10
gt; 5 20
gt; ...
gt; ...
gt; nth
gt;
gt; I use manual selection (e.g. =STDEV(B1:B3, B5:B6, etc...) that covers the
40
gt; consecutive rows. B4 here is zero so I did not select it.
gt;
gt; My rows is getting bigger and bigger, how could I formulate this?
gt;
gt; Thanks in advance
gt;
gt; Carlos
gt;
gt;
Hi Max,

First of all thanks for your advice. I'm afraid I did not give more detail
of what my problem is.

To give you more idea of what my goal is all about, say I limit my criteria
to 5 consecutive instead of the 40 (to shorten my example below). Column C
to analyze the 5 consecutive results ignoring Column A with zero :
A B C
1 15
2 20
3 10
0 20
4 10
5 20 5.00 =STDEV(B5:B6,B1:B3)
0 15 blank because A7=0
6 10 5.48 =STDEV(B8,B5:B6,B2:B3)
7 20 5.48 =STDEV(B8:B9,B5:B6,B3)
0 30 blank because A10=0
8 25 6.71 =STDEV(B11,B8:B9,B5:B6)
etc.

I hope you get what I mean.

Any further help will be appreciated.

Thanks.

Carlos

quot;Maxquot; wrote:

gt; Try in say, C1: =STDEV(IF(A1:A40lt;gt;0,B1:B40))
gt; Array-enter the formula, i.e. press CTRL SHIFT ENTER
gt; instead of just pressing ENTER
gt; --
gt; Rgds
gt; Max
gt; xl 97
gt; ---
gt; Singapore, GMT 8
gt; xdemechanik
gt; savefile.com/projects/236895
gt; --
gt; quot;Carlosquot; gt; wrote in message
gt; ...
gt; gt; Hi,
gt; gt;
gt; gt; Could someone please advise.
gt; gt;
gt; gt; I want to get the standard deviation based on criteria. It looks like
gt; this.
gt; gt; Criteria is 40 consecutive values ignoring 0 in Column A.
gt; gt; A B
gt; gt; 1 50
gt; gt; 2 20
gt; gt; 3 10
gt; gt; 0 20
gt; gt; 4 10
gt; gt; 5 20
gt; gt; ...
gt; gt; ...
gt; gt; nth
gt; gt;
gt; gt; I use manual selection (e.g. =STDEV(B1:B3, B5:B6, etc...) that covers the
gt; 40
gt; gt; consecutive rows. B4 here is zero so I did not select it.
gt; gt;
gt; gt; My rows is getting bigger and bigger, how could I formulate this?
gt; gt;
gt; gt; Thanks in advance
gt; gt;
gt; gt; Carlos
gt; gt;
gt; gt;
gt;
gt;
gt;

Perhaps it would be simpler/quicker to just add a header row, then
autofilter on col A for rows lt;gt; 0, and copy gt; paste the filtered rows to
another sheet. Then over there, just put in say, C6: =STDEV(B2:B6) and copy
down
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
quot;Carlosquot; gt; wrote in message
...
gt; Hi Max,
gt;
gt; First of all thanks for your advice. I'm afraid I did not give more detail
gt; of what my problem is.
gt;
gt; To give you more idea of what my goal is all about, say I limit my
criteria
gt; to 5 consecutive instead of the 40 (to shorten my example below). Column
C
gt; to analyze the 5 consecutive results ignoring Column A with zero :
gt; A B C
gt; 1 15
gt; 2 20
gt; 3 10
gt; 0 20
gt; 4 10
gt; 5 20 5.00 =STDEV(B5:B6,B1:B3)
gt; 0 15 blank because A7=0
gt; 6 10 5.48 =STDEV(B8,B5:B6,B2:B3)
gt; 7 20 5.48 =STDEV(B8:B9,B5:B6,B3)
gt; 0 30 blank because A10=0
gt; 8 25 6.71 =STDEV(B11,B8:B9,B5:B6)
gt; etc.
gt;
gt; I hope you get what I mean.
gt;
gt; Any further help will be appreciated.
gt;
gt; Thanks.
gt;
gt; Carlos
Max,

Appreciate your advice. I have thought of this before posting to the group.
I would have around 20 worksheets in one file and each worksheet would
contain thousands of records. I have found this approach time consuming.

Carlos

quot;Maxquot; wrote:

gt; Perhaps it would be simpler/quicker to just add a header row, then
gt; autofilter on col A for rows lt;gt; 0, and copy gt; paste the filtered rows to
gt; another sheet. Then over there, just put in say, C6: =STDEV(B2:B6) and copy
gt; down
gt; --
gt; Rgds
gt; Max
gt; xl 97
gt; ---
gt; Singapore, GMT 8
gt; xdemechanik
gt; savefile.com/projects/236895
gt; --
gt; quot;Carlosquot; gt; wrote in message
gt; ...
gt; gt; Hi Max,
gt; gt;
gt; gt; First of all thanks for your advice. I'm afraid I did not give more detail
gt; gt; of what my problem is.
gt; gt;
gt; gt; To give you more idea of what my goal is all about, say I limit my
gt; criteria
gt; gt; to 5 consecutive instead of the 40 (to shorten my example below). Column
gt; C
gt; gt; to analyze the 5 consecutive results ignoring Column A with zero :
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 =STDEV(B5:B6,B1:B3)
gt; gt; 0 15 blank because A7=0
gt; gt; 6 10 5.48 =STDEV(B8,B5:B6,B2:B3)
gt; gt; 7 20 5.48 =STDEV(B8:B9,B5:B6,B3)
gt; gt; 0 30 blank because A10=0
gt; gt; 8 25 6.71 =STDEV(B11,B8:B9,B5:B6)
gt; gt; etc.
gt; gt;
gt; gt; I hope you get what I mean.
gt; gt;
gt; gt; Any further help will be appreciated.
gt; gt;
gt; gt; Thanks.
gt; gt;
gt; gt; Carlos
gt;
gt;
gt;

Apologies, I'm out of suggestions to offer you, Carlos.
Perhaps others may step in with something for you.
Hang around awhile. All the best.
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
quot;Carlosquot; gt; wrote in message
news
gt; Max,
gt;
gt; Appreciate your advice. I have thought of this before posting to the
group.
gt; I would have around 20 worksheets in one file and each worksheet would
gt; contain thousands of records. I have found this approach time consuming.
gt;
gt; Carlos
Mark,

Thanks. Am still trying. Now am using DSTDEV(database,field,criteria) but
got stuck with criteria and not sure if this function is appropriate.

Carlos

quot;Maxquot; wrote:

gt; Apologies, I'm out of suggestions to offer you, Carlos.
gt; Perhaps others may step in with something for you.
gt; Hang around awhile. All the best.
gt; --
gt; Rgds
gt; Max
gt; xl 97
gt; ---
gt; Singapore, GMT 8
gt; xdemechanik
gt; savefile.com/projects/236895
gt; --
gt; quot;Carlosquot; gt; wrote in message
gt; news
gt; gt; Max,
gt; gt;
gt; gt; Appreciate your advice. I have thought of this before posting to the
gt; group.
gt; gt; I would have around 20 worksheets in one file and each worksheet would
gt; gt; contain thousands of records. I have found this approach time consuming.
gt; gt;
gt; gt; Carlos
gt;
gt;
gt;

gt; Mark,

The name is Max, Carlos lt;ggt;. Think the prob is not with the function, but
the manner in which you want to fill the formula to exclude the preceding
lines with zeros above, which lines do not appear to bear any regular
sequence. I see that you've since re-posted and got some insight's from Peo.
And from your latest post there, good to see you seem to have worked out the
solution, too !
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
Sorry Max, I don't know why I keyed in here my boss' name (Mark)... perhaps
because he is the culprit of all these problems!!

I agree with you, selections of no regular sequence is the real problem.
With that array formula that I have worked out (hope is OK), do you see any
problem, say speed in performing calculation when I reach the expected 10,000
records, considering that the array is reading the very first row till the
end row?

Happy New Year Max.

Carlos

quot;Maxquot; wrote:

gt; gt; Mark,
gt;
gt; The name is Max, Carlos lt;ggt;. Think the prob is not with the function, but
gt; the manner in which you want to fill the formula to exclude the preceding
gt; lines with zeros above, which lines do not appear to bear any regular
gt; sequence. I see that you've since re-posted and got some insight's from Peo.
gt; And from your latest post there, good to see you seem to have worked out the
gt; solution, too !
gt; --
gt; Rgds
gt; Max
gt; xl 97
gt; ---
gt; Singapore, GMT 8
gt; xdemechanik
gt; savefile.com/projects/236895
gt; --
gt;
gt;
gt;

gt; .. With that array formula that I have worked out (hope is OK),
gt; do you see any problem, say speed in performing calculation
gt; when I reach the expected 10,000 records,
gt; considering that the array is reading the very first row till the
gt; end row?

I'm not sure about the specifics in your case, Carlos, but I'd usually set
the calc mode to quot;Manualquot; for workbooks loaded with intensive calcs, and
press F9 key to calc when ready.
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--