Excel 2003 Problem

[ChrisR9040]

This is a pretty interesting problem with Excel that I have never see
before and has frustrated me to no end. Any input or suggestions woul
be greatly appreciated.

All I am trying to do take to values from two columns (A and B) and ad
them together into a third (C). The formula is correct, but the en
result is zero, as if it does not recognize the data as numerical.
Attempting to change the format does nothing, I cannot add dolla
signs, or decimal points to the numbers, and I've even opened a ne
spreadsheet, pre-formatted the columns to be numbers, and used 'past
special-values' and there is no change. If I retype the number in th
cell (A or B), it recognizes the new data as a number, and cell C i
adjusted accordingly.

Any help you can offer would be awesome because if I don't figure thi
out, I will have to retype hundreds of rows.

Thank

 

[Gary Smith]

Most likely the numbers are formatted as text. To fix this, first select
columns A and B, then click Format > Cells > Number tab and set the format
to General or the numeric format of your choice. You won't see any change
yet.

Now enter a zero into some unused cell not in either of those columns,
right-click the cell and click Copy. Next select all of the cells in
columns A and B with content, then click Edit > Paste Special and select
both "Values" and operation "Add", then click OK. This adds zero to every
value in the selected range and forces the format to numeric.

This is a pretty interesting problem with Excel that I have never seen
before and has frustrated me to no end. Any input or suggestions would
be greatly appreciated.

All I am trying to do take to values from two columns (A and B) and add
them together into a third (C). The formula is correct, but the end
result is zero, as if it does not recognize the data as numerical.
Attempting to change the format does nothing, I cannot add dollar
signs, or decimal points to the numbers, and I've even opened a new
spreadsheet, pre-formatted the columns to be numbers, and used 'paste
special-values' and there is no change. If I retype the number in the
cell (A or B), it recognizes the new data as a number, and cell C is
adjusted accordingly.

 

[ChrisR9040]

Gary,

Thank you, it was a good idea, but it didn't work...nothing changed,
and it still doesn't add columns A and B. Any other ideas?

Chris

 

[Harlan Grove]

ChrisR9040 wrote...
....

All I am trying to do take to values from two columns (A and B) and add
them together into a third (C). The formula is correct, but the end
result is zero, as if it does not recognize the data as numerical.
Attempting to change the format does nothing, I cannot add dollar
signs, or decimal points to the numbers, and I've even opened a new
spreadsheet, pre-formatted the columns to be numbers, and used 'paste
special-values' and there is no change. If I retype the number in the
cell (A or B), it recognizes the new data as a number, and cell C is
adjusted accordingly.

....

Almost certainly the 'numbers' on columns A and B are actually text.
Not formatted as text, but of datatype text (or string). Changing their
number format should have no effect on them. They won't appear any
different, and their sum won't change.

If you're summing them as =SUM(A2:B2), then Excel would treat both
cells as numeric zeros if they contain text. However, if your formula
were =A2+B2, Excel would sum them since Excel automatically converts
text operands of arithmetic operators to numbers if possible (when
Transition Formula Evaluation is disabled, which is the default state).

You could change the formulas to use the addition operator, +, rather
than the SUM function, but you may be better off converting columns A
and B to numbers by selecting each in sequence and running Data > Text
to Columns, choosing Fixed Width and IMMEDIATELY clicking the Finish
button.

Usually it's a better idea to ask Excel-specific questions in
Excel-specific newsgroups.

 

[Gary Smith]

If you followed the directions I gave and they didn't work for you, I have
no more to offer. I've never seen a situation where that failed. Try
posting your question in a group dedicated to Excel.