Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standard Deviation
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standard Deviation
Try in say, C1: =STDEV(IF(A1:A40<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 http://savefile.com/projects/236895 -- "Carlos" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standard Deviation
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 "Max" wrote: Try in say, C1: =STDEV(IF(A1:A40<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 http://savefile.com/projects/236895 -- "Carlos" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standard Deviation
Perhaps it would be simpler/quicker to just add a header row, then
autofilter on col A for rows < 0, and copy 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 http://savefile.com/projects/236895 -- "Carlos" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standard Deviation
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 "Max" wrote: Perhaps it would be simpler/quicker to just add a header row, then autofilter on col A for rows < 0, and copy 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 http://savefile.com/projects/236895 -- "Carlos" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standard Deviation
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 http://savefile.com/projects/236895 -- "Carlos" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standard Deviation
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 "Max" wrote: 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 http://savefile.com/projects/236895 -- "Carlos" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standard Deviation
Mark,
The name is Max, Carlos <g. 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 http://savefile.com/projects/236895 -- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standard Deviation
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 "Max" wrote: Mark, The name is Max, Carlos <g. 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 http://savefile.com/projects/236895 -- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standard Deviation
.. 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? I'm not sure about the specifics in your case, Carlos, but I'd usually set the calc mode to "Manual" for workbooks loaded with intensive calcs, and press F9 key to calc when ready. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Standard Deviation
Might do the same. Many thanks.
Carlos "Max" wrote: .. 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? I'm not sure about the specifics in your case, Carlos, but I'd usually set the calc mode to "Manual" for workbooks loaded with intensive calcs, and press F9 key to calc when ready. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a bell graph for standard deviation? | Charts and Charting in Excel | |||
Relative standard deviation in Excel | Excel Worksheet Functions | |||
feel that the STANDARD DEVIATION formula on Excel is incorrect !! | Excel Worksheet Functions | |||
How to calculate 2 standard deviation? | Excel Worksheet Functions | |||
Is there a standard deviation calculation in Excel? | Excel Worksheet Functions |