How to get #value! to return a value of 0?
 

My formula is like this: =A2+B2+C2. However, some of the values in the B2
column are "--," indicating "not applicable". When I use the formula, it
interprets this as text, of course, and returns the error value of #VALUE!
I want it to treat these as zeroes. How can I do this? I tried doing a FIND
AND REPLACE, but it did not recognize any of the "-- " values in the FIND.
Can anybody help?

=SUM(A2:C2) would be the easiest way. SUM will ignore text.

tj

"rhodesv" wrote:

My formula is like this: =A2+B2+C2. However, some of the values in the B2
column are "--," indicating "not applicable". When I use the formula, it
interprets this as text, of course, and returns the error value of #VALUE!
I want it to treat these as zeroes. How can I do this? I tried doing a FIND
AND REPLACE, but it did not recognize any of the "-- " values in the FIND.
Can anybody help?

Thanks! That's helpful. What do I do about adding columns that are not
contiguous? Do I do SUM(A2+C2+F2) ?

"tjtjjtjt" wrote:

=SUM(A2:C2) would be the easiest way. SUM will ignore text.

tj

"rhodesv" wrote:

My formula is like this: =A2+B2+C2. However, some of the values in the B2
column are "--," indicating "not applicable". When I use the formula, it
interprets this as text, of course, and returns the error value of #VALUE!
I want it to treat these as zeroes. How can I do this? I tried doing a FIND
AND REPLACE, but it did not recognize any of the "-- " values in the FIND.
Can anybody help?

You replace the colon with commas:
=SUM(A2,C2,F2)

Hope that helps,
tj

"rhodesv" wrote:

Thanks! That's helpful. What do I do about adding columns that are not
contiguous? Do I do SUM(A2+C2+F2) ?

"tjtjjtjt" wrote:

=SUM(A2:C2) would be the easiest way. SUM will ignore text.

tj

"rhodesv" wrote:

My formula is like this: =A2+B2+C2. However, some of the values in the B2
column are "--," indicating "not applicable". When I use the formula, it
interprets this as text, of course, and returns the error value of #VALUE!
I want it to treat these as zeroes. How can I do this? I tried doing a FIND
AND REPLACE, but it did not recognize any of the "-- " values in the FIND.
Can anybody help?

You already have the best answer but an alternative is to enter a zero in the
cell instead of "--". Format the cell entry to Number - Category Accounting,
resulting in the display of a single dash instead of the number 0.

"tjtjjtjt" wrote:

You replace the colon with commas:
=SUM(A2,C2,F2)

Hope that helps,
tj

"rhodesv" wrote:

Thanks! That's helpful. What do I do about adding columns that are not
contiguous? Do I do SUM(A2+C2+F2) ?

"tjtjjtjt" wrote:

=SUM(A2:C2) would be the easiest way. SUM will ignore text.

tj

"rhodesv" wrote:

My formula is like this: =A2+B2+C2. However, some of the values in the B2
column are "--," indicating "not applicable". When I use the formula, it
interprets this as text, of course, and returns the error value of #VALUE!
I want it to treat these as zeroes. How can I do this? I tried doing a FIND
AND REPLACE, but it did not recognize any of the "-- " values in the FIND.
Can anybody help?