IF IS NULL PROBLEM

[Guest]

A B C D E
F
Shipment # Quote # Date Actual GP Gross Revenue Total Points
LAX 2056512 406213 02/10/05 65.41% $82.50 2.5

For the data above, I have a VLOOKUP table to determine points for GP %
achieved. However, .5 point is awarded if a Quote # is entered. I have the
following formula in Column F which returns the correct amount of points, but
in a blank line, the formula also displays .5 I thought if I entered IS
NOT NULL in the formula, I would get the correct answer but I get an error
message. What am I doing incorrectly?

=(VLOOKUP(D2,$H$2:$J$6,3)+IF(B2>0,0.5,0))

 

[Tim C]

Try:

+IF(ISNUMBER(B2),0.5,0)

or

+ISNUMBER(B2)/2

Tim C

 

[Guest]

Thanks for your response, Tim, but neither worked

 

[BobT]

instead of just >0 you could test b2 for a range
=if(and(b2>0,b2<1000000000),.5,0)
you could use the parameters of your quote numbers for the
range.
A single space as well as other possible returns are > 0