# Resolving a formula...

#### gtd526

##### Active Member
Hello,
I'm trying to resolve a formula.
=IF(AND((\$B2<0),(\$D2>\$B2*-1)),"Yes", "")

When B2 & B3=pk (as below), its not recognized as a number.
How can I turn pk to 0 in the formula above?
I just want to see if the MOV (margin of victory) is > the spread (B:B).

Thank you.

Book1
ABCDEF
1TeamsATSCover %MOVATS +/-MOV > ATS
2INDpk75%125.1#VALUE!
3CLEpk50%0-1.2#VALUE!
4MIA8.050%-14.2
5SF-8.050%92.6Yes
Sheet1
Cell Formulas
RangeFormula
C2:C5C2=IFERROR(VLOOKUP(\$A2,'[NFL.xlsm]ATS Trends'!\$A\$2:\$F\$43,4,0),"")
D2:D5D2=IFERROR(VLOOKUP(\$A2,'[NFL.xlsm]ATS Trends'!\$A\$2:\$F\$65,5,0),"")
E2:E5E2=IFERROR(VLOOKUP(\$A2,'[NFL.xlsm]ATS Trends'!\$A\$2:\$F\$44,6,0),"")
F2:F5F2=IF(AND((\$B2<0),(\$D2>\$B2*-1)),"Yes", "")

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Joe4

One way:
Excel Formula:
``=IF(AND((IF(ISNUMBER(\$B2),\$B2,0)<0),(\$D2>IF(ISNUMBER(\$B2),\$B2,0)*-1)),"Yes", "")``

#### gtd526

##### Active Member
One way:
Excel Formula:
``=IF(AND((IF(ISNUMBER(\$B2),\$B2,0)<0),(\$D2>IF(ISNUMBER(\$B2),\$B2,0)*-1)),"Yes", "")``
Thank you for the formula.
Works perfectly.

#### Peter_SSs

##### MrExcel MVP, Moderator

Excel Formula:
``=IF(AND((N(\$B2)<0),(\$D2>N(\$B2)*-1)),"Yes", "")``

#### Joe4

=IF(AND((N(\$B2)<0),(\$D2>N(\$B2)*-1)),"Yes", "")
I keep forgetting about the N function. It does make it a bit shorter!

#### gtd526

##### Active Member

Excel Formula:
``=IF(AND((N(\$B2)<0),(\$D2>N(\$B2)*-1)),"Yes", "")``
Thank you

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Thanks for the follow-up.

Replies
3
Views
77
Replies
3
Views
105
Replies
9
Views
276
Replies
1
Views
64
Replies
4
Views
172