Converting a string to value of zero

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

Kevin Sprinkel

I am checking the delta between two columns of numbers in
a third column for the purpose of comparing two rounds of
a construction cost estimate.

Occasionally, what was in the scope the first time around
has been removed (because they can't afford it), in which
case I'd like the text string to have the value of zero.

Two different attempts yield the errors shown below:

Desired Result (Convert any string to zero)

A B C
DD SD Delta
1 2,000 3,000 -1,000
2 3,500 2,500 1,000
3 NIC 2,000 -2,000


Results with C3 = A3 - B3:

A B C
3 NIC 2,000 #VALUE!

Results with C3 = IIF(Type(A3)=1,A3,0):

A B C
3 NIC 2,000 #NAME?

Any suggestions?

Kevin Sprinkel
Becker & Frondorf
 
This formula will sort the problem, if the string appears in your first or
second column.

IF(AND(ISNUMBER(A1),ISNUMBER(B1)),A1-B1,IF(ISNUMBER(A1),A1,IF(AND(ISNUMBER(A
1)=FALSE,ISNUMBER(B1)=FALSE),0,0-B1)))

Maybe you can figure out how to shorten it.

Cheers
wbez

I am checking the delta between two columns of numbers in
a third column for the purpose of comparing two rounds of
a construction cost estimate.

Occasionally, what was in the scope the first time around
has been removed (because they can't afford it), in which
case I'd like the text string to have the value of zero.

Two different attempts yield the errors shown below:

Desired Result (Convert any string to zero)

A B C
DD SD Delta
1 2,000 3,000 -1,000
2 3,500 2,500 1,000
3 NIC 2,000 -2,000


Results with C3 = A3 - B3:

A B C
3 NIC 2,000 #VALUE!

Results with C3 = IIF(Type(A3)=1,A3,0):

A B C
3 NIC 2,000 #NAME?

Any suggestions?

Kevin Sprinkel
Becker & Frondorf
 
-----Original Message-----
This formula will sort the problem, if the string appears in your first or
second column.

IF(AND(ISNUMBER(A1),ISNUMBER(B1)),A1-B1,IF(ISNUMBER (A1),A1,IF(AND(ISNUMBER(A
1)=FALSE,ISNUMBER(B1)=FALSE),0,0-B1)))

Maybe you can figure out how to shorten it.

Cheers
wbez
Click to expand...

Thanks, WBEZ, that solved the problem. I shortened the
last nested If to:

IF(ISNUMBER(B1),0-B1,0)

Thanks!
 
Back
Top