Nested IF Statements

S

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?
 
S

steve

Sorry, should be

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

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),""))
 
S

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

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.
 
S

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

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,"")),""))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top