OR function not returning “TRUE” when parameters m

Question

I have a large data set and have created a number of rules I want to use to filter out some of the unnecessary data points.

Here is a simplified example of some data:

    A         B        C        D
1 PIPPIP      0    0.411298  PIPPIP
2 PIPPIP     0.6    0.40924  PIPPIP
3 PIPPIP      1    0.378689  PIPPIP
4 PIPPIP      1    0.75456   PIPPIP
5 PIPPIP     0.8   0.967534  PIPPIP
6 PIPPYG     0.9   0.865426  PIPPYG
7 PIPPYG     0.9   0.789654  PIPPYG

An example rule would be to separate all data points which have both B > 0.89 and C value of > 0.749 if D=PIPPIP or >0.849 if D=PIPPYG

To do this I am using the formula:

=AND(B1>0.89,(OR((AND(D1="PIPPIP",C1>0.749)),(AND(D1="PIPPYG",C1>0.849))))) 

However, this always returns a FALSE value even for points which meet both criteria. I know that there must be an error in the formula but I can't figure out what the error is. Would someone please point it out to me?

Or is the problem that I can't nest OR functions like I have?

I would really appreciate if someone would walk me through this.

Thanks.

Answer 1

I tried your exact example and there are 2 rows that returns true. I did check the formula and it seems fine as well:

if it's not the result you are expecting, maybe you could add a column and tell us which one are suppose to return true/false.

Answer 2

In this case the best you can do is the following:

• Select the cell with the formula.
• Evaluate the formula step by step with the built-in evaluator in Excel (Tab Formulas>Evaluate Formula> Evaluate):

• Find your error and fix it.