I have a population that I belive it will be represent a Gaussian (normal)
distribution if I am able to identify and remove the outliers properly. I now
understand I cannot use the Grubb's Test as outlined in a couple of websites
I found (see http://www.graphpad.com/articles/outlier.htm and
http://www.itl.nist.gov/div898/softw...r/grubtest.htm) because I have many outliers and the distribution is not normal.

Based on a discussion I found on this forums (subject: "how do I identify
statistical outliers with excel", dated: 1/7/2007), I decided to use the the
Interquartile Range method (IQR); but the following formula is giving me an
annoying #VALUE! error:

=if(or(D1 < A1 - 3*B1, D1 A2 + 3*B1), "outlier?", "")

I modified that formula so that D1 is the value I am testing, A1 is the
value of the 1st quartile, B1 is the value between the 3rd quartile minus the
1st quartile, and A2 is the value of the 3rd quartile. But I got the same
error message. Then I removed the spaces in the formula, but I still get the
same error. It is very frustating, and I don't know how to fix this.

Any suggestions?

This works for me: =IF(OR(D1<(A1-(3*B1)),D1(A2+(3*B1))),"outlier?","")

You're missing many parentheses in your formula below; also the spaces make
it hard to read what you're trying to do.

See if my proposal works.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Quco" wrote:

I have a population that I belive it will be represent a Gaussian (normal)
distribution if I am able to identify and remove the outliers properly. I now
understand I cannot use the Grubb's Test as outlined in a couple of websites
I found (see http://www.graphpad.com/articles/outlier.htm and
http://www.itl.nist.gov/div898/softw...r/grubtest.htm) because I have many outliers and the distribution is not normal.

Based on a discussion I found on this forums (subject: "how do I identify
statistical outliers with excel", dated: 1/7/2007), I decided to use the the
Interquartile Range method (IQR); but the following formula is giving me an
annoying #VALUE! error:

=if(or(D1 < A1 - 3*B1, D1 A2 + 3*B1), "outlier?", "")

I modified that formula so that D1 is the value I am testing, A1 is the
value of the 1st quartile, B1 is the value between the 3rd quartile minus the
1st quartile, and A2 is the value of the 3rd quartile. But I got the same
error message. Then I removed the spaces in the formula, but I still get the
same error. It is very frustating, and I don't know how to fix this.

Any suggestions?

Thanks Dave,

This is is very interesting, now I am not getting the error message #VALUE!;
now I am getting the word FALSE in the cell as a result instead getting the
text "outlier?".


"Dave F" wrote:

This works for me: =IF(OR(D1<(A1-(3*B1)),D1(A2+(3*B1))),"outlier?","")

You're missing many parentheses in your formula below; also the spaces make
it hard to read what you're trying to do.

See if my proposal works.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Quco" wrote:

I have a population that I belive it will be represent a Gaussian (normal)
distribution if I am able to identify and remove the outliers properly. I now
understand I cannot use the Grubb's Test as outlined in a couple of websites
I found (see http://www.graphpad.com/articles/outlier.htm and
http://www.itl.nist.gov/div898/softw...r/grubtest.htm) because I have many outliers and the distribution is not normal.

Based on a discussion I found on this forums (subject: "how do I identify
statistical outliers with excel", dated: 1/7/2007), I decided to use the the
Interquartile Range method (IQR); but the following formula is giving me an
annoying #VALUE! error:

=if(or(D1 < A1 - 3*B1, D1 A2 + 3*B1), "outlier?", "")

I modified that formula so that D1 is the value I am testing, A1 is the
value of the 1st quartile, B1 is the value between the 3rd quartile minus the
1st quartile, and A2 is the value of the 3rd quartile. But I got the same
error message. Then I removed the spaces in the formula, but I still get the
same error. It is very frustating, and I don't know how to fix this.

Any suggestions?

Well, FALSE is an improvement because it indicates the formula is calculating.

IF statements work with the following logic: IF A, Then B, Else C. Stated
another way: If A, value if A is true, value if A is false. Absent a value
in the false argument, FALSE is returned.

So: check your formula and the values in the cells that the formula refers
to. Are the values in the cells numbers? Are they formatted as numbers? Do
you have all your parentheses in order? Etc.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Quco" wrote:

Thanks Dave,

This is is very interesting, now I am not getting the error message #VALUE!;
now I am getting the word FALSE in the cell as a result instead getting the
text "outlier?".


"Dave F" wrote:

This works for me: =IF(OR(D1<(A1-(3*B1)),D1(A2+(3*B1))),"outlier?","")

You're missing many parentheses in your formula below; also the spaces make
it hard to read what you're trying to do.

See if my proposal works.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Quco" wrote:

I have a population that I belive it will be represent a Gaussian (normal)
distribution if I am able to identify and remove the outliers properly. I now
understand I cannot use the Grubb's Test as outlined in a couple of websites
I found (see http://www.graphpad.com/articles/outlier.htm and
http://www.itl.nist.gov/div898/softw...r/grubtest.htm) because I have many outliers and the distribution is not normal.

Based on a discussion I found on this forums (subject: "how do I identify
statistical outliers with excel", dated: 1/7/2007), I decided to use the the
Interquartile Range method (IQR); but the following formula is giving me an
annoying #VALUE! error:

=if(or(D1 < A1 - 3*B1, D1 A2 + 3*B1), "outlier?", "")

I modified that formula so that D1 is the value I am testing, A1 is the
value of the 1st quartile, B1 is the value between the 3rd quartile minus the
1st quartile, and A2 is the value of the 3rd quartile. But I got the same
error message. Then I removed the spaces in the formula, but I still get the
same error. It is very frustating, and I don't know how to fix this.

Any suggestions?

It appears to be right. I don't know what is wrong. I am comparing:
=IF(OR(G3027<(G3035-(3*G3040)),G3027(G3037+(3*G3040))),"outlier?","")
=IF(OR(D1<(A1-(3*B1)),D1(A2+(3*B1))),"outlier?","")

My formula vs. your notation for the formula I am using.

The cells are all values resulting from other formulas on my spreadsheet;
all are numbers.

I was reading more about the OR function, it appears that the OR function
always return a True or FALSE value. But how do I make understand Excel that
it needs to treat the content of the OR formula as valid (in this case) so
that the result is printed as "outlier?" ???

Maybe there is something I am missing... do you think is because my cells
are not number-values but numbers resulting from other formulas? everything
else seems to be working properly on my Excel spreadsheet.

"Dave F" wrote:

Well, FALSE is an improvement because it indicates the formula is calculating.

IF statements work with the following logic: IF A, Then B, Else C. Stated
another way: If A, value if A is true, value if A is false. Absent a value
in the false argument, FALSE is returned.

So: check your formula and the values in the cells that the formula refers
to. Are the values in the cells numbers? Are they formatted as numbers? Do
you have all your parentheses in order? Etc.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Quco" wrote:

Thanks Dave,

This is is very interesting, now I am not getting the error message #VALUE!;
now I am getting the word FALSE in the cell as a result instead getting the
text "outlier?".


"Dave F" wrote:

This works for me: =IF(OR(D1<(A1-(3*B1)),D1(A2+(3*B1))),"outlier?","")

You're missing many parentheses in your formula below; also the spaces make
it hard to read what you're trying to do.

See if my proposal works.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Quco" wrote:

I have a population that I belive it will be represent a Gaussian (normal)
distribution if I am able to identify and remove the outliers properly. I now
understand I cannot use the Grubb's Test as outlined in a couple of websites
I found (see http://www.graphpad.com/articles/outlier.htm and
http://www.itl.nist.gov/div898/softw...r/grubtest.htm) because I have many outliers and the distribution is not normal.

Based on a discussion I found on this forums (subject: "how do I identify
statistical outliers with excel", dated: 1/7/2007), I decided to use the the
Interquartile Range method (IQR); but the following formula is giving me an
annoying #VALUE! error:

=if(or(D1 < A1 - 3*B1, D1 A2 + 3*B1), "outlier?", "")

I modified that formula so that D1 is the value I am testing, A1 is the
value of the 1st quartile, B1 is the value between the 3rd quartile minus the
1st quartile, and A2 is the value of the 3rd quartile. But I got the same
error message. Then I removed the spaces in the formula, but I still get the
same error. It is very frustating, and I don't know how to fix this.

Any suggestions?

It's hard to say what the issue is. What are the actual values in G3027,
etc. Are they formatted as numbers, as opposed to text?

OR tests for TRUE or FALSE values: =OR(1+2=10,2+2=4) resolves to TRUE
because one calculation is TRUE. On the other hand =OR(1+2=2,2+2=24332)
resolves to FALSE because neither calculation is true. Therefore your
formula says: "If [calculation A] OR [calculation B] is correct, or both are
correct, then consider it an outlier, else return an empty string."

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Quco" wrote:

It appears to be right. I don't know what is wrong. I am comparing:
=IF(OR(G3027<(G3035-(3*G3040)),G3027(G3037+(3*G3040))),"outlier?","")
=IF(OR(D1<(A1-(3*B1)),D1(A2+(3*B1))),"outlier?","")

My formula vs. your notation for the formula I am using.

The cells are all values resulting from other formulas on my spreadsheet;
all are numbers.

I was reading more about the OR function, it appears that the OR function
always return a True or FALSE value. But how do I make understand Excel that
it needs to treat the content of the OR formula as valid (in this case) so
that the result is printed as "outlier?" ???

Maybe there is something I am missing... do you think is because my cells
are not number-values but numbers resulting from other formulas? everything
else seems to be working properly on my Excel spreadsheet.

"Dave F" wrote:

Well, FALSE is an improvement because it indicates the formula is calculating.

IF statements work with the following logic: IF A, Then B, Else C. Stated
another way: If A, value if A is true, value if A is false. Absent a value
in the false argument, FALSE is returned.

So: check your formula and the values in the cells that the formula refers
to. Are the values in the cells numbers? Are they formatted as numbers? Do
you have all your parentheses in order? Etc.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Quco" wrote:

Thanks Dave,

This is is very interesting, now I am not getting the error message #VALUE!;
now I am getting the word FALSE in the cell as a result instead getting the
text "outlier?".


"Dave F" wrote:

This works for me: =IF(OR(D1<(A1-(3*B1)),D1(A2+(3*B1))),"outlier?","")

You're missing many parentheses in your formula below; also the spaces make
it hard to read what you're trying to do.

See if my proposal works.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Quco" wrote:

I have a population that I belive it will be represent a Gaussian (normal)
distribution if I am able to identify and remove the outliers properly. I now
understand I cannot use the Grubb's Test as outlined in a couple of websites
I found (see http://www.graphpad.com/articles/outlier.htm and
http://www.itl.nist.gov/div898/softw...r/grubtest.htm) because I have many outliers and the distribution is not normal.

Based on a discussion I found on this forums (subject: "how do I identify
statistical outliers with excel", dated: 1/7/2007), I decided to use the the
Interquartile Range method (IQR); but the following formula is giving me an
annoying #VALUE! error:

=if(or(D1 < A1 - 3*B1, D1 A2 + 3*B1), "outlier?", "")

I modified that formula so that D1 is the value I am testing, A1 is the
value of the 1st quartile, B1 is the value between the 3rd quartile minus the
1st quartile, and A2 is the value of the 3rd quartile. But I got the same
error message. Then I removed the spaces in the formula, but I still get the
same error. It is very frustating, and I don't know how to fix this.

Any suggestions?

You shouldn't be getting FALSE from that formula, whatever the inputs,
because both outcomes of the IF are defined as strings.
Did you literally copy and paste from the formula bar to the newsgroup
posting, or might something have been mistyped?
Try copying back from your posting below to the spreadsheet, or try copying
=IF(OR(G3027<G3035-3*G3040,G3027G3037+3*G3040),"outlier?","")
into your spreadsheet to check.
--
David Biddulph

"Quco" wrote in message
...
It appears to be right. I don't know what is wrong. I am comparing:
=IF(OR(G3027<(G3035-(3*G3040)),G3027(G3037+(3*G3040))),"outlier?","")
=IF(OR(D1<(A1-(3*B1)),D1(A2+(3*B1))),"outlier?","")

My formula vs. your notation for the formula I am using.

The cells are all values resulting from other formulas on my spreadsheet;
all are numbers.

I was reading more about the OR function, it appears that the OR function
always return a True or FALSE value. But how do I make understand Excel
that
it needs to treat the content of the OR formula as valid (in this case) so
that the result is printed as "outlier?" ???

Maybe there is something I am missing... do you think is because my cells
are not number-values but numbers resulting from other formulas?
everything
else seems to be working properly on my Excel spreadsheet.

"Dave F" wrote:

Well, FALSE is an improvement because it indicates the formula is
calculating.

IF statements work with the following logic: IF A, Then B, Else C.
Stated
another way: If A, value if A is true, value if A is false. Absent a
value
in the false argument, FALSE is returned.

So: check your formula and the values in the cells that the formula
refers
to. Are the values in the cells numbers? Are they formatted as numbers?
Do
you have all your parentheses in order? Etc.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Quco" wrote:

Thanks Dave,

This is is very interesting, now I am not getting the error message
#VALUE!;
now I am getting the word FALSE in the cell as a result instead getting
the
text "outlier?".


"Dave F" wrote:

This works for me:
=IF(OR(D1<(A1-(3*B1)),D1(A2+(3*B1))),"outlier?","")

You're missing many parentheses in your formula below; also the
spaces make
it hard to read what you're trying to do.

See if my proposal works.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Quco" wrote:

I have a population that I belive it will be represent a Gaussian
(normal)
distribution if I am able to identify and remove the outliers
properly. I now
understand I cannot use the Grubb's Test as outlined in a couple of
websites
I found (see http://www.graphpad.com/articles/outlier.htm and
http://www.itl.nist.gov/div898/softw...r/grubtest.htm)
because I have many outliers and the distribution is not normal.

Based on a discussion I found on this forums (subject: "how do I
identify
statistical outliers with excel", dated: 1/7/2007), I decided to
use the the
Interquartile Range method (IQR); but the following formula is
giving me an
annoying #VALUE! error:

=if(or(D1 < A1 - 3*B1, D1 A2 + 3*B1), "outlier?", "")

I modified that formula so that D1 is the value I am testing, A1 is
the
value of the 1st quartile, B1 is the value between the 3rd quartile
minus the
1st quartile, and A2 is the value of the 3rd quartile. But I got
the same
error message. Then I removed the spaces in the formula, but I
still get the
same error. It is very frustating, and I don't know how to fix
this.

Any suggestions?