I'm working with a complicated Excel workbook that includes many
formulas. Some of them use + and - for adding and subtracting, and
some of them use the SUM function.
I need them all to use the SUM function. Is there a macro out there or
other tool that will convert all the formulas to use the SUM function?
The reason I need to do this is that Excel handles errors differently
for SUM than for +. If I get a #VALUE! error because of a non-numeric
data input, it won't go away on fixing the data input if the formulas
use + instead of SUM.
Please ask for clarification if you need it.
Thanks!
Hi
Here is the Process to Change ur +'s and -'s to Sum Function.
1) Open the Excel Sheet
2) Press the Crtl + ~ Key
3) Excel would show the Formulas instead of the Result Numbers like it
normally used to
4) Press Crtl + H or Find Replace, REPLACE Window Opens
5) In the FIND WHAT key in " =+ " and in the REPLACE WITH " =SUM( "
(dont use the Double Quotes)
6) Press the REPLACE ALL Button on the Replace Window
7) Again, Press Crtl + H or Find Replace, REPLACE Window Opens
8) In the FIND WHAT key in " + " (Plus Symbol or Addition) and in the
REPLACE WITH " , " (Comma) (dont use the Double Quotes)
9) Press the REPLACE ALL Button on the Replace Window
10) Press the Crtl + ~ Key again and your results will show normally as usual
Note :
1) Your can select particluar cells and do the Process or cant do it
for the Entire Sheet. But suggest you to Sellect the Cells in which
the Formulas are to be replaced from +'s to SUM Functions.
2) You cannot replace a -'s to SUM Function. Suggest you to retain the
formulas as is.
Hope this helps iTunes XML data inside Excel::
Jun 13, 2006 How can the exported playlist data from iTunes, which comes native in XML format , be brought into Microsoft Excel?
http://answers.google.com/answers/threadview/id/737813.htmlHOME |
To supplement 09091977-ga's comments, you can replace the minus signs too.
First search and replace "=-" with "=sum(-". Then search and replace
"-" with ",-". This will put the right text into your sum formulas,
except for any negatives that come first in the formula. These will
now say "sum(,-". Search and replace this string with "sum(-" and
you're all set.
Incidentally, I can't replicate the problem you're trying to solve.
Using simple + and - formulas, I don't get a #VALUE! error after
correcting a non-text input. (I'm using Excel 2002) A list of top 500 publicly listed companies in the ::
The question: Replace "top 5000" with "all publicly listed companies in the top New question I need a list (in excel spreadsheet) of the publicly listed
http://answers.google.com/answers/threadview?id=589318HOME |
analysis of the problems with todays web browsers ::
Page viewing is truly all they excel at. Movement between pages and the . to quickly convert a Visual Basic application to a Web-ready application in an
http://answers.google.com/answers/threadview/id/172336.htmlHOME |
 Where's The Advantage In Windows Genuine Advantage?
 Stocks Bounce After S&P Joins Bear Market
|
