Formula Error

[gtd526]

Hello,

I want to perform a subtraction in place of "Yes".
IFERROR(IF(AND($B5<0,$Z5>ABS($B5)),"Yes",IF(AND($B5<0,$Z5=ABS($B5)),"Tie","")),"") = shows Yes, which is correct (AO6).
I want it to perform a Subtraction instead of "Yes".
IFERROR(IF(AND($B5<0,$Z5>ABS($B5)),Z5-ABS(B5),IF(AND($B5<0,$Z5=ABS($B5)),"Tie","")),"") = shows "Blank" (AO5).

If I put this formula in another cell(M5) Z5-ABS(B5), I get the correct answer. See below.

NFL.xlsm
	B											M													Z															AO
5	-2.5											3.5													6															3.5
6	-7																								8															Yes
Weekly Picks

Cell Formulas
Range	Formula
M5	M5	=Z5-ABS(B5)
Z5:Z6	Z5	=IFERROR(VLOOKUP($A5,'ATS Trends'!$A$2:$F$65,5,0),"")
AO5	AO5	=IFERROR(IF(AND($B5<0,$Z5>ABS($B5)),Z5-ABS(B5),IF(AND($B5<0,$Z5=ABS($B5)),"Tie","")),"")
AO6	AO6	=IFERROR(IF(AND($B6<0,$Z6>ABS($B6)),"Yes",IF(AND($B6<0,$Z6=ABS($B6)),"Tie","")),"")

 

[Joe4]

It seems to work for me.

If I put:

=IFERROR(IF(AND($B5<0,$Z5>ABS($B5)),Z5-ABS(B5),IF(AND($B5<0,$Z5=ABS($B5)),"Tie","")),"")

in cell A05, it returns 3.5

If it does not for you, then verify that the values in B5 and Z5 are actually entered as numbers and not text.
You can do that by entering these formulas anywhere and see if they both return TRUE.
=ISNUMBER(B5)
=ISNUMBER(Z5)
If either returns FALSE, then you have "numbers entered as text".

 

[gtd526]

It seems to work for me.

If I put:

=IFERROR(IF(AND($B5<0,$Z5>ABS($B5)),Z5-ABS(B5),IF(AND($B5<0,$Z5=ABS($B5)),"Tie","")),"")

in cell A05, it returns 3.5

If it does not for you, then verify that the values in B5 and Z5 are actually entered as numbers and not text.
You can do that by entering these formulas anywhere and see if they both return TRUE.
=ISNUMBER(B5)
=ISNUMBER(Z5)
If either returns FALSE, then you have "numbers entered as text".

Yes. The formula is OK.
Once I plugged in the "ISNUMBER", it was coming back as True.
So I looked at Conditional Formatting, and there was the problem.
If it was <> Tie, it would apply White font. There was the problem.
It was correct the whole time.
Thanks for the reply.

 

[Joe4]

So I looked at Conditional Formatting, and there was the problem.
If it was <> Tie, it would apply White font. There was the problem.
It was correct the whole time.

Ah, so it was returning the correct value, you just couldn't see it!