Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
=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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
How do I check the conditions of 2 fields to return a result | Excel Discussion (Misc queries) | |||
Return Column header, if row value is > X | Excel Discussion (Misc queries) | |||
Carriage Return in Excel | Excel Discussion (Misc queries) | |||
How do I return to the start of the next row - EXCEL 2002? | Excel Discussion (Misc queries) |