software

Hi,

Can anybody help me SUM the following two numbers *without* changing
the orginal cell contents? Excel sees them as text and the second
number is a minus number. I'd rather do this with regular formulae than
VBA, but I'll settle for either Thanks a mill.$1,418.26
($2,189.74)
---------------
-771.48If there are no other invisible characters involved

=SUM(--(A1:A2))

entered with ctrl shift amp; enter

or

=SUMPRODUCT(--(A1:A2))

entered normally

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;rkdquot; gt; wrote in message ups.com...
gt;
gt; Hi,
gt;
gt; Can anybody help me SUM the following two numbers *without* changing
gt; the orginal cell contents? Excel sees them as text and the second
gt; number is a minus number. I'd rather do this with regular formulae than
gt; VBA, but I'll settle for either
gt;
gt;
gt; Thanks a mill.
gt;
gt;
gt; $1,418.26
gt; ($2,189.74)
gt; ---------------
gt; -771.48
gt;
Or, as an alternative,
=VALUE(A1) VALUE(A2)
That was easier than I expected -- ended up using SUMPRODUCT!

Thanks a lot -- much appreciated.
Thanks also -- good to know!
Actually, without thinking I used the formula against the numbers I did
a Find/Replace on which worked fine but
when I tried it against the numbers in example format I'm getting
'#VALUE!' errors -- any ideas?Thanks.
The dollar and parentheses are part of the text strings in the example,
so from what I can tell they need to be stripped
out and the numbers in parentheses converted to negative before the
formulae above will work!?

Also, as I said above, I need to keep the original cells as they are
with the dollar sign and parentheses.

thanks again.
Ok, I ended up doing this a rather unneat way but it works. I made an
out-of-sight column
corresponding to the original column with this formula:

=IF(ISTEXT(L6),--(IF(LEFT(L6,1)=quot;(quot;,-RIGHT(LEFT(L6,LEN(L6)-1),LEN(LEFT(L6,LEN(L6)-1))-2),RIGHT(L6,LEN(L6)-1))),)

This stripped out the the $,(,) characters and adds a '-' for negative
numbers in parentheses. Then I AutoSum the column and
referenced the result where I needed it.

If anybody knows a neater way I'd be interested to see/hear it.