Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Converting Pluses and Times to Sum and Product Functions ???
I have converted Lotus 1-2-3 spreadsheets that are just shy of functioning
properly in the Excel environment. Lotus apparently treats text in cells as 0 or 1 values so that when they are added or multiplied (with operands) it has no net effect on the value that results. Excel returns an error message when using operands to sum/multiply values with text. I did discover that Excel does what I need it to do when using the =SUM() and =Product() functions. I updated all the formulas by hand in one sheet and then found that there are 69 more sheets and the formulas are irregular. Does anyone have any idea how to write a VBA macro that will parse the spreadsheet and convert all the formulas to use SUM and/or PRODUCT instead of " + " and " * "? Example Convert: " =ROUND ( (+CO34 +DA34 -AA33) * DD34,0) " To: " =ROUND (PRODUCT (SUM (CO34,DA34,-AA33) ,DD34) ,0) " or To: " =ROUND (PRODUCT (SUM (CO34) + SUM (CO34) - SUM (AA33) ,1) * PRODUCT (DD34,1) ,0) " Both methods seem to give me the same result. The second way is easier for me to approach, but I haven't been able to nail it down. Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Product of Hrs & minutes times rate per hr to give a cost | Excel Discussion (Misc queries) | |||
In using the formula for Product (A2:A4,2), why times 2? | Excel Worksheet Functions | |||
Excel Functions- Product Array | Excel Worksheet Functions | |||
Automatic Product Functions | Excel Worksheet Functions | |||
Converting Times | Excel Programming |