Adding Value within a single cell...

[Brian]

I have a spread sheet with multiple values in individual cells separated by
commas. (i.e. 1,6,23,2,63,399)

Can I add these values and display the result in another cell while leaving
the original cell intact?

Thank.s

 

[Jason Morin]

One way:

1. Press Ctrl+F3 and define the name "addcell" (no
quotes) as:

=EVALUATE("SUM("&A1&")")

2. In the cell to the right put:

=addcell

3. Be careful. From H. Grove: "Pasting formulas with
names referring to XLM functions into different
worksheets than that containing the copied cells happily
crashes Excel 2000 and 97".

HTH
Jason
Atlanta, GA

 

[Jason Morin]

Better yet, use:

=EVALUATE("SUM("&INDIRECT("rc[-1]",0)&")")

This will always evaluate the cell one column to the left.

Jason

 

[Patti]

Another possibility is to copy, paste special, values into the other cell and
then use edit, replace to change the commas into plus signs (in the other
cells only) and then add an equals sign to the front of the equation.

 

[Brian]

Thanks for the replies, very helpful.