Conditional Standard Deviation?

PerthGuy · Mar 6th, 2002, 02:56 AM

Is it possible to create a formula (= ) for calculating the standard deviation with conditions, ie calculate only the values >-2 and <2 in a data set?

Aladin Akyurek · Mar 6th, 2002, 03:42 AM

Quote:

On 2002-03-06 01:56, PerthGuy wrote:
Is it possible to create a formula (= ) for calculating the standard deviation with conditions, ie calculate only the values >-2 and <2 in a data set?

Array-enter:

=STDEV(IF((A1:A100>-2)*(A1:A100<2),A1:A100)

where A1:A100 houses the values of interest.

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Aladin

Dave Hawley · Mar 6th, 2002, 03:49 AM

Hi PerthGuy

Perth WA ?

Array formulas can have an adverse effect on Excel re-calculations. Personally I would use the:

DSTDEV Function

All the DFunctions work off the same pricipal and the Excel help on them is very good. Just type Database functions into the help. These functions are designed to work with large data tables are are far quicker.

I also have examples an ideas for these great functions here:

http://www.ozgrid.com/download/default.htm

under:

DFunctionsWithValidation.zip

PerthGuy · Mar 6th, 2002, 11:05 PM

Thanks Aladin and Dave.

Aladin's suggestion works fine, however I have another question: I want to be able to remove data from column A and replace it with either more or less data. this means I have to keep adjusting the formula to suit, ie from A1:A10 to A1:A20; or from A1:A20 to A1:A15, etc. I have tried naming the column by clicking on the A at the top, and using the name (Data) as a reference, but I keep getting a #DIV/0! message. Any suggestions?

Here is what my formula looks like:

{=STDEV(IF((Data>-0.5)*(Data<0.5),Data))}

Dave Hawley · Mar 6th, 2002, 11:21 PM

Hi PerthGuy

I will leave this to Aladin as I honestly think you are heading down the wrong track and I don't do that to people! If you would like some help the Dfunctions I am more than willing to help. I

Aladin Akyurek · Mar 6th, 2002, 11:45 PM

Quote:

On 2002-03-06 22:05, PerthGuy wrote:
Thanks Aladin and Dave.

Aladin's suggestion works fine, however I have another question: I want to be able to remove data from column A and replace it with either more or less data. this means I have to keep adjusting the formula to suit, ie from A1:A10 to A1:A20; or from A1:A20 to A1:A15, etc. I have tried naming the column by clicking on the A at the top, and using the name (Data) as a reference, but I keep getting a #DIV/0! message. Any suggestions?

Here is what my formula looks like:

{=STDEV(IF((Data>-0.5)*(Data<0.5),Data))}

Lets say that column A from A1 on houses the values of interest.

In B1 enter:

=MATCH(9.99999999999999E+307,A:A)

In B2 enter: -0.5
In B3 enter: 0.5
In B4 array-enter:

=STDEV(IF((OFFSET($A$1,0,0,$B$1,1)>$B$2)*(OFFSET($A$1,0,0,$B$1,1)<$B$3),OFFSET($A$1,0,0,$B$1,1)))

Aladin

Ivan F Moala · Mar 7th, 2002, 12:04 AM

Quote:

On 2002-03-06 22:21, Dave Hawley wrote:
Hi PerthGuy

I will leave this to Aladin as I honestly think you are heading down the wrong track and I don't do that to people! If you would like some help the Dfunctions I am more than willing to help. I

Dave, an array formula in this situation
may be advisable aslong as he dosen't
have a book full of these and UDF's....that
will take up excels calculation engine time.

Ivan

Dave Hawley · Mar 7th, 2002, 12:31 AM

You are right in the repsect that a couple wont hurt. But It's very unlikely (once a unwary user) discovers then they will stop at one. Besides I believe if we are going to help somebody we should do it properly and not give a short term solution.

PerthGuy · Mar 7th, 2002, 06:22 AM

Aladin,

Success. Thank you for your help.

Dave,

Yes, I'm from Perth, WA. Where are you located?

Thank you for your assistance. I would like to learn about the Dfunctions sometime, but Aladin's solution works fine for this particular work I'm doing. But I have Bookmarked the web site you suggested for when I need to do this.

I do, however, have another question out of curiosity: Is it possible to separate the data from col A into two columns, ie <0 in col A, >0 in col B?

PerthGuy · Mar 7th, 2002, 10:00 PM

Aladin,

Success. Thank you for your help.

Dave,

Yes, I'm from Perth, WA. Where are you located?

Thank you for your assistance. I would like to learn about the Dfunctions sometime, but Aladin's solution works fine for this particular work I'm doing. But I have Bookmarked the web site you suggested for when I need to do this.

I do, however, have another question out of curiosity: Is it possible to separate the data from col A into two columns, ie <0 in col A, >0 in col B?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2002, 02:56 AM	#1
PerthGuy New Member Join Date: Mar 2002 Posts: 4	Is it possible to create a formula (= ) for calculating the standard deviation with conditions, ie calculate only the values >-2 and <2 in a data set?

Mar 6th, 2002, 03:49 AM	#3
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi PerthGuy Perth WA ? Array formulas can have an adverse effect on Excel re-calculations. Personally I would use the: DSTDEV Function All the DFunctions work off the same pricipal and the Excel help on them is very good. Just type Database functions into the help. These functions are designed to work with large data tables are are far quicker. I also have examples an ideas for these great functions here: http://www.ozgrid.com/download/default.htm under: DFunctionsWithValidation.zip

Mar 6th, 2002, 11:05 PM	#4
PerthGuy New Member Join Date: Mar 2002 Posts: 4	Thanks Aladin and Dave. Aladin's suggestion works fine, however I have another question: I want to be able to remove data from column A and replace it with either more or less data. this means I have to keep adjusting the formula to suit, ie from A1:A10 to A1:A20; or from A1:A20 to A1:A15, etc. I have tried naming the column by clicking on the A at the top, and using the name (Data) as a reference, but I keep getting a #DIV/0! message. Any suggestions? Here is what my formula looks like: {=STDEV(IF((Data>-0.5)*(Data<0.5),Data))}

Mar 6th, 2002, 11:21 PM	#5
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi PerthGuy I will leave this to Aladin as I honestly think you are heading down the wrong track and I don't do that to people! If you would like some help the Dfunctions I am more than willing to help. I

Mar 7th, 2002, 12:31 AM	#8
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	You are right in the repsect that a couple wont hurt. But It's very unlikely (once a unwary user) discovers then they will stop at one. Besides I believe if we are going to help somebody we should do it properly and not give a short term solution.

Mar 7th, 2002, 06:22 AM	#9
PerthGuy New Member Join Date: Mar 2002 Posts: 4	Aladin, Success. Thank you for your help. Dave, Yes, I'm from Perth, WA. Where are you located? Thank you for your assistance. I would like to learn about the Dfunctions sometime, but Aladin's solution works fine for this particular work I'm doing. But I have Bookmarked the web site you suggested for when I need to do this. I do, however, have another question out of curiosity: Is it possible to separate the data from col A into two columns, ie <0 in col A, >0 in col B?

Mar 7th, 2002, 10:00 PM	#10
PerthGuy New Member Join Date: Mar 2002 Posts: 4	Aladin, Success. Thank you for your help. Dave, Yes, I'm from Perth, WA. Where are you located? Thank you for your assistance. I would like to learn about the Dfunctions sometime, but Aladin's solution works fine for this particular work I'm doing. But I have Bookmarked the web site you suggested for when I need to do this. I do, however, have another question out of curiosity: Is it possible to separate the data from col A into two columns, ie <0 in col A, >0 in col B?