 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2004How 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 > 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) ? > =SUM(A2:C2) would be the easiest way. SUM will ignore text. > [quoted text clipped - 6 lines] > > 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 > Thanks! That's helpful. What do I do about adding columns that are not > contiguous? Do I do SUM(A2+C2+F2) ? [quoted text clipped - 9 lines] > > > 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. > You replace the colon with commas: > =SUM(A2,C2,F2) [quoted text clipped - 15 lines] > > > > AND REPLACE, but it did not recognize any of the "-- " values in the FIND. > > > > Can anybody help? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.