Help with Formula Please

[Bob R.]

I have the following formula in cell c1 in a spreadsheet:

=IF(D1>A1,IF(B1="over","+"),IF(D1<A1,IF(B1="over","-",IF(D1<A1,IF(B1="under","+"),IF(D1>A1,IF(B1="under","-"))))))

200 over - 195
200 over + 205
200 under FALSE 205
200 under + 195

What I've done above is test the formula for all four possible
outcomes. As you can see the third outcome in the table (the fourth
argument in the formula) is false, but I want it to read "-".

I have changed the formula around every way I can think of, but I
can't make it come out right for that fourth argument.

Can anybody see what I'm doing wrong?

Thanks a lot.

 

[Dan E]

Bob,

Your statement is arranged incorrectly.

=IF(D1>A1, IF(B1="over","+",IF(B1="under","-")), IF(B1="over","-",IF(B1="under","+")))

will work. But, would need to be modified if you were concerned
about the results when A1=D1.

Long winded explanation:

=IF(D1>A1,IF(B1="over","+"),IF(D1<A1,IF(B1="over","-",IF(D1<A1,IF(B1="under","+"),IF(D1>A1,IF(B1="under","-"))))))

Make a logic tree

IF D1 > A1
IF B1 = "over"
+
IF B1 <> "over"
FALSE
IF D1 <= A1
IF D1 < A1
IF B1 = "over"
-
IF B1 <> "over"
IF D1 < A1
IF B1 = "under"
+
IF B1 <> "under"
IF D1 > A1 <--- This statement is stuck beneath a test to ensure D1 < A1
IF B1 = "under"
-
IF B1 <> "under"
FALSE

Simplified

=IF(D1>A1, IF(B1="over","+",IF(B1="under","-")), IF(B1="over","-",IF(B1="under","+")))

IF D1 > A1 ----> All results if D1 > A1 need to be under this branch
IF B1 = "over"
+
IF B1 <> "over"
IF B1 = "under"
-
IF B1 <> "under"
FALSE
IF D1 <= A1 ----> All results if D1 <= A1 need to be under this branch
IF B1 = "over"
-
IF B1 <> "over"
IF B1 = "under"
+
IF B1 <> "under"
FALSE

Dan E

 

[Ken Wright]

=IF(D1>A1,IF(B1="over","+"),IF...............

It's the way you have structured your IF statement. For your third example,
D1>A1, so when it encounters that right at the start of your formula it kicks
straight into the 'value if true' argument, and that is closed at B1 being Over.
It's not, so you simply get a FALSE returned.

You can use AND to help you here, or even a TEXT function, eg:-

=IF(AND(D1>A1,B1="over"),"+",IF(AND(D1>A1,B1="under"),"-",IF(AND(D1<A1,B1="over"
),"-","+")))
(Only ned to test for 3 of them, and then return the 4th if none of the first 3
criteria are met)

or using TEXT function

=IF(B1="Under",LEFT(TEXT(A1-D1,"+#,##0;-#,##0")),LEFT(TEXT(D1-A1,"+#,##0;-#,##0"
)))

 

[Bob R.]

But, would need to be modified if you were concerned
about the results when A1=D1.

Thanks Dan and Ken. I will read your answers later and study them. I
appreciate your responses.

I was going to insert an if statement for when A1=D1 so that it would
show up as a "t" for tie. I was going to do that later, because I
didn't think it would be too difficult. Now I realize that I could
have been wrong about that.

I guess I need help with that too.

Thanks again you guys, I really appreciate it.

Bob

 

[Ken Wright]

=IF(AND(D1>A1,B1="over"),"+",IF(AND(D1>A1,B1="under"),"-",IF(AND(D1<A1,B1="over"
),"-",IF(AND(D1<A1,B1="under"),"+","Tie"))))

or

=IF(D1=A1,"Tie",IF(B1="Under",LEFT(TEXT(A1-D1,"+#,##0;-#,##0")),LEFT(TEXT(D1-A1,
"+#,##0;-#,##0"))))