Nested IF Statements

[steve]

Hi,

I'm looking to return a result of blank, 1, or -1, depending on the
value of another cell. Here's what I have so far:

=IF((ISBLANK(A1),"",(IF((AND(ISNUMBER(A1),(IF(A1>SQRT(AVERAGE(A$1:A
$54))),1,-1)))

What I want to do is:
- If the cell A is blank, cell B should be blank
- If cell A is a number then I want to check to see if the cell is
greater than the square root of the average of column A.
- If cell A is greater than the square root of the averages of column
A than cell B should be 1.
- If cell B is less than the square root of the average of column A
than cell B should be -1.

What I originally was using was:
=IF((AND(ISBLANK(A1))),"",(IF((A1>SQRT(AVERAGE(A$1:A$54))),1,-1)))

The problem I ran into was that if cell A1 and A2 were both zero than
column b would result as -1 since it was false.

What am I missing?

 

[steve]

Sorry, should be

=IF((ISBLANK(A1),"",(IF((AND(ISNUMBER(A1),(IF(SQRT(A1)>SQRT(AVERAGE(A
$1:A
$54))),1,-1)))

 

[T. Valko]

In your explanation you don't mention that you're wanting the sqrt of A1 to
meet certain conditions but in your formula you're testing the sqrt of A1.

Assuming this formula can be interpreted to be your true intentions:

=IF((ISBLANK(A1),"",(IF((AND(ISNUMBER(A1),(IF(SQRT(A1)>SQRT(AVERAGE(A$1:A$54))),1,-1)))

Try it like this:

=IF(A1="","",IF(COUNT(A1),IF(SQRT(A1)>SQRT(AVERAGE(A$1:A$54)),1,-1),""))

 

[steve]

Thanks. I used the formula as listed below. If I cell A1 is 0, A2 is 0
the cell B3 (I copy the formula down) lists as -1.

 

[T. Valko]

You said:

- If cell A is greater than the square root of the
averages of column A than cell B should be 1.
- If cell B is less than the square root of the
average of column A than cell B should be -1.

In the sample you site, the sqrt and avg are equal which falls outside the
above description.

So, what should the result be?

--
Biff
Microsoft Excel MVP


Thanks. I used the formula as listed below. If I cell A1 is 0, A2 is 0
the cell B3 (I copy the formula down) lists as -1.

 

[steve]

Sorry, I did attempt to catch that with:

"Thanks. I used the formula as listed below. If I cell A1 is 0, A2 is
0
the cell B3 (I copy the formula down) lists as -1."

however I wasn't explicit enough with the expected outcome. In this
example B3 should be blank.

 

[T. Valko]

Try this...

=IF(A1="","",IF(COUNT(A1),IF(SQRT(A1)>SQRT(AVERAGE(A$1:A$54)),1,IF(SQRT(A1)<SQRT(AVERAGE(A$1:A$54)),-1,"")),""))