Converting a string to value of zero

[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

 

[wbez]

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

 

[Kevin Sprinkel]

-----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

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

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

Thanks!