Resolving a formula...

gtd526

Active Member
Joined
Jul 30, 2013
Messages
261
Office Version
  1. 2019
Platform
  1. Windows
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", "")
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,467
Office Version
  1. 365
Platform
  1. Windows
One way:
Excel Formula:
=IF(AND((IF(ISNUMBER($B2),$B2,0)<0),($D2>IF(ISNUMBER($B2),$B2,0)*-1)),"Yes", "")
 
Solution

gtd526

Active Member
Joined
Jul 30, 2013
Messages
261
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows
What about?

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,467
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,960
Messages
5,575,245
Members
412,651
Latest member
caitlincole
Top