Nested Conditions

[whippler]

I have a dynamic table below, and I want the last column to display one of four things based on the third column:

geo a b
north 7.5 0.1 FALSE
south 9.4 0.4
west 7.0 0.3
central 6.9 -0.1
blank 5.8 -0.1
blank 8.1 -0.6
blank 7.6 0.8
BLANK
BLANK


1. "BLANK" (IF FIRST COLUMN SAYS "BLANK")

2. 1 (IF THIRD COLUMN VALUE IS >0.5)
3. 0 (IF THIRD COLUMN VALUE IS BETWEEN -0.5 AND -1)
4. -1 (IF THIRD COLUMN VALUE IS -1)

This is the formula I used, but getting "FALSE" as my output.

=IF(A2="BLANK","BLANK", IF(AND(C2<-0.5,C2>-1),0, IF(C2<-1,-1, IF(C2>0.5,1))))

Any thoughts?

Many thanks.

[/quote]

 

[Seti]

The false comes from the last if condition, since you didn't specify what to do when this is hit, it defaults to false. The first four data points do not meet any of your 4 conditions, so they fall to the last if and produce the false. The last 5 meet condition 1 and should produce blank as the answer.

 

[eliW]

Seti is right,
Also what happens when values are between -0.5 - +0.5 ?

Eli

 

[whippler]

duh, thanks!