Total Cell Error

[Diane Walker]

We are running Excel 2003. The data was imported into Excel. We want to
get the total of 3 columns. However, we got the error message in the Total
cell "#VALUE!". We resize the Total cell and it still did not work. The 3
columns were formatted as General. We reformatted to Numeric with 2
Decimals. We still got the same error message in the Total cell "#VALUE!".
We reformatted the 3 columns to Currency, Accounting, and Custom. The
Total cell still shows "#VALUE!". Do you have any suggestions on how to fix
the values in the 3 columns? Thanks.

 

[Guest]

Sounds like maybe the values you are trying to sum may have been copied from
elsewhere.

In another cell, type 1
Copy the 1.
Highlight your three columns
Right-Click and select Paste Special
Select Multiply
Click OK
see if it adds up now...

 

[Diane Walker]

Thanks very much for your prompt response, Sean. This file was imported
into Excel format using Delimited format. I tried your suggestions and
still got the same error message "#VALUE!" . Do you have any other
suggestions? Thanks.

 

[Guest]

We can narrow down, perhaps.

If you select one of the sum cells and click the = to the left of the
formula bar, it will provide which portion of the sum is not being recognized.

To be safe, it may be helpful if we could see a small sample of the data you
are attempting to sum.

Oh, and one other thought.. when you go to sum the columns, do you have,
say, =sum(A2:A4)? What formula are you putting in the sum cells?

 

[Diane Walker]

Thanks very much for your prompt response, Sean.

I selected one of the sum cells and click the = to the left of the formula
bar, I do not see anything. I used both formulas for sum cells
"=sum(A2:A4)" and "+A2+A3+A4". Do I need to format the columns in any
formats (Number, Currency, Accounting, etc.)? The columns came in as
General format. Do I need to format the Sum cells first before setting the
formulas? Below is the sample data. I need to add 675.00+923.30+933.30.
Please let me know if you have any other questions. Thanks.

675.00 923.30 933.30
675.00 826.70 770.00
925.00 826.70 860.00
750.00 910.00 893.30
575.00 920.00 826.70
350.00 910.00 750.00

 

[Diane Walker]

Sean,

I formatted the 3 columns and the total column to Number with 2 decimals. I
moved the cursor on one of the sum cells where the "!" is, it said "Value
used in the formula is of the wrong data type". I setup the formula in the
Sum cell as "+A2+A3+A4". Let me know if you have any questions or
suggestions. Thanks.

 

[Diane Walker]

Sean,

Sorry for sending you several messages. You know what I just found out. If
I formatted the 3 columns and the total column to Number with 2 decimals and
then manually reenter the numbers in the 3 columns, the total column works.
I noticed that the numbers in the 3 columns were entered left-justified
instead of
right-justified. It seemed that the 3 columns were formatted as Text
instead of Numeric when the file is imported. Maybe the solution is to
reimport the file and format those columns as Numeric unless you have other
suggestions. Thanks.

 

[Gord Dibben]

Diane

You can Re-import but changing the format to Numeric won't do you any good.

Excel must be forced to see the data as Numeric.

One way is to format all to Number then copy an empty cell.

Select the data copy then, in place, Paste Special>Add>OK>Esc.

Or select the data and Data>Text to Columns>Next>Next>Column Data
Format>General>Finish.

Now format to Number


Gord Dibben MS Excel MVP

 

[Diane Walker]

Gord,

Thank you very much for your suggestions.

Both methods do not work. I still got the error message "#VALUE!" in the
Total cells by using the second method. The first method of copying by
using Paste Special do not copy the columns. When I use Paste Special, the
columns are empty. Do you have any other suggestions. Thanks.

 

[Gord Dibben]

I guess we should establish whether or not your data is being treated as text,
which I believe it is.

In a cell enter =ISNUMBER(cellref) where cellref is one of your cells with
data.

If text, this return FALSE

I think you may be confused by my statement "Select the data copy then, in
place, Paste Special>Add>OK>Esc."

It had an extra "copy" in it and should have read "Select the data then, in
place, Paste Special>Add>OK>Esc.

Apologies for the mistake.


Gord

 

[Diane Walker]

Thank you very much for your response, Gord

Thank you for clarification. The copy (in place) method still did not work.
I still got the error message "#VALUE!" in Total cells.

I typed the ISNUMBER(cellref) on those 3 columns, the return was FALSE even
though I have tried to format those columns as Numeric, Accounting, or
Custom.
Thanks.

 

[Gord Dibben]

Diane

Not "copy" in place..........."paste special" in place.

One more time through the steps, with a twist..............

Format all cells to General or Number

Select an unused(empty) cell.

Type the number 1 in that cell.

Edit>Copy just that cell.

Select all the data cells.

Edit>Paste Special>Multiply>OK>Esc.

When happy, clear the 1 from the lone cell.


Gord


Paste Special(in place)

 

[Diane Walker]

Gord,

Thank you very much for your prompt response and information. I formatted
all cells to General or Numeric and followed the instruction. The Total
cells still got the error message "#VALUE!".

If I formatted the columns as Numeric and manually retyped the numbers, I do
not get an error message in Total cells. However, there are about 400
numbers in each column, I might make a mistake when I retype those numbers.
But, I might have to do it if I don't have any choice. Thanks very much for
your help.

 

[Gord Dibben]

Diane

If you wish, you can send the workbook to my email.

I am curious now.

Change the AT and DOT to appropriate punctuation.


Gord

 

[Diane Walker]

Gord,

Thank you very much for offering to help. I just emailed you the file.
Thanks.

 

[Gord Dibben]

Diane sent me the workbook.

All "numbers" had the dreaded non-breaking 160 character at right side.

Found by using Chip Pearson's CellView add-in, which I would not be without.

http://www.cpearson.com/excel/CellView.htm

Did an Edit>Replace and all's well.

David McRitchie's TRIMALL macro would have also stripped the 160 char.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


Gord

 

[Diane Walker]

Gord,

Your instructions work!!! You are the best!!! Thank you very much for your
assistance. I really appreciate your time for working on this problem.
Your time and information are greatly appreciated. You have saved me a
tremendous amount of time to manually rekeying those numbers.

Again, thank you very much.