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.

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" wrote:

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.

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

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" wrote:

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

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



"Sean Timmons" wrote in message
...
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" wrote:

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

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 SpecialAddOKEsc.

Or select the data and DataText to ColumnsNextNextColumn Data
FormatGeneralFinish.

Now format to Number


Gord Dibben MS Excel MVP

On Wed, 31 Jan 2007 09:38:15 -0800, "Diane Walker" wrote:

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

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" <gorddibbATshawDOTca wrote in message
...
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 SpecialAddOKEsc.

Or select the data and DataText to ColumnsNextNextColumn Data
FormatGeneralFinish.

Now format to Number


Gord Dibben MS Excel MVP

On Wed, 31 Jan 2007 09:38:15 -0800, "Diane Walker"
wrote:

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

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 SpecialAddOKEsc."

It had an extra "copy" in it and should have read "Select the data then, in
place, Paste SpecialAddOKEsc.

Apologies for the mistake.


Gord

On Thu, 1 Feb 2007 07:33:11 -0800, "Diane Walker" wrote:

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" <gorddibbATshawDOTca wrote in message
.. .
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 SpecialAddOKEsc.

Or select the data and DataText to ColumnsNextNextColumn Data
FormatGeneralFinish.

Now format to Number


Gord Dibben MS Excel MVP

On Wed, 31 Jan 2007 09:38:15 -0800, "Diane Walker"
wrote:

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

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" <gorddibbATshawDOTca wrote in message
...
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 SpecialAddOKEsc."

It had an extra "copy" in it and should have read "Select the data then,
in
place, Paste SpecialAddOKEsc.

Apologies for the mistake.


Gord

On Thu, 1 Feb 2007 07:33:11 -0800, "Diane Walker"
wrote:

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" <gorddibbATshawDOTca wrote in message
. ..
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 SpecialAddOKEsc.

Or select the data and DataText to ColumnsNextNextColumn Data
FormatGeneralFinish.

Now format to Number


Gord Dibben MS Excel MVP

On Wed, 31 Jan 2007 09:38:15 -0800, "Diane Walker"
wrote:

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

"Sean Timmons" wrote in
message
...
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" wrote:

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.

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.

EditCopy just that cell.

Select all the data cells.

EditPaste SpecialMultiplyOKEsc.

When happy, clear the 1 from the lone cell.


Gord


Paste Special(in place)
On Thu, 1 Feb 2007 13:03:23 -0800, "Diane Walker" wrote:

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" <gorddibbATshawDOTca wrote in message
.. .
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 SpecialAddOKEsc."

It had an extra "copy" in it and should have read "Select the data then,
in
place, Paste SpecialAddOKEsc.

Apologies for the mistake.


Gord

On Thu, 1 Feb 2007 07:33:11 -0800, "Diane Walker"
wrote:

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" <gorddibbATshawDOTca wrote in message
...
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 SpecialAddOKEsc.

Or select the data and DataText to ColumnsNextNextColumn Data
FormatGeneralFinish.

Now format to Number


Gord Dibben MS Excel MVP

On Wed, 31 Jan 2007 09:38:15 -0800, "Diane Walker"
wrote:

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.

"Sean Timmons" wrote in message
...
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" wrote:

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.

"Sean Timmons" wrote in
message
...
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" wrote:

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.

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" <gorddibbATshawDOTca wrote in message
...
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.

EditCopy just that cell.

Select all the data cells.

EditPaste SpecialMultiplyOKEsc.

When happy, clear the 1 from the lone cell.


Gord


Paste Special(in place)
On Thu, 1 Feb 2007 13:03:23 -0800, "Diane Walker"
wrote:

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" <gorddibbATshawDOTca wrote in message
. ..
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 SpecialAddOKEsc."

It had an extra "copy" in it and should have read "Select the data then,
in
place, Paste SpecialAddOKEsc.

Apologies for the mistake.


Gord

On Thu, 1 Feb 2007 07:33:11 -0800, "Diane Walker"
wrote:

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" <gorddibbATshawDOTca wrote in message
m...
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 SpecialAddOKEsc.

Or select the data and DataText to ColumnsNextNextColumn Data
FormatGeneralFinish.

Now format to Number


Gord Dibben MS Excel MVP

On Wed, 31 Jan 2007 09:38:15 -0800, "Diane Walker"
wrote:

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.

"Sean Timmons" wrote in
message
...
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" wrote:

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.

"Sean Timmons" wrote in
message
...
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" wrote:

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.

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

On Thu, 1 Feb 2007 15:15:49 -0800, "Diane Walker" wrote:

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" <gorddibbATshawDOTca wrote in message
.. .
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.

EditCopy just that cell.

Select all the data cells.

EditPaste SpecialMultiplyOKEsc.

When happy, clear the 1 from the lone cell.


Gord


Paste Special(in place)
On Thu, 1 Feb 2007 13:03:23 -0800, "Diane Walker"
wrote:

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" <gorddibbATshawDOTca wrote in message
...
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 SpecialAddOKEsc."

It had an extra "copy" in it and should have read "Select the data then,
in
place, Paste SpecialAddOKEsc.

Apologies for the mistake.


Gord

On Thu, 1 Feb 2007 07:33:11 -0800, "Diane Walker"
wrote:

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" <gorddibbATshawDOTca wrote in message
om...
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 SpecialAddOKEsc.

Or select the data and DataText to ColumnsNextNextColumn Data
FormatGeneralFinish.

Now format to Number


Gord Dibben MS Excel MVP

On Wed, 31 Jan 2007 09:38:15 -0800, "Diane Walker"
wrote:

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.

"Sean Timmons" wrote in
message
...
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" wrote:

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.

"Sean Timmons" wrote in
message
...
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" wrote:

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.

Gord,

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Thu, 1 Feb 2007 15:15:49 -0800, "Diane Walker"
wrote:

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" <gorddibbATshawDOTca wrote in message
. ..
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.

EditCopy just that cell.

Select all the data cells.

EditPaste SpecialMultiplyOKEsc.

When happy, clear the 1 from the lone cell.


Gord


Paste Special(in place)
On Thu, 1 Feb 2007 13:03:23 -0800, "Diane Walker"
wrote:

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" <gorddibbATshawDOTca wrote in message
m...
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 SpecialAddOKEsc."

It had an extra "copy" in it and should have read "Select the data
then,
in
place, Paste SpecialAddOKEsc.

Apologies for the mistake.


Gord

On Thu, 1 Feb 2007 07:33:11 -0800, "Diane Walker"
wrote:

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" <gorddibbATshawDOTca wrote in message
news:55q1s2174uhrufr9eeel9l27ekq4u0rghb@4ax. com...
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 SpecialAddOKEsc.

Or select the data and DataText to ColumnsNextNextColumn Data
FormatGeneralFinish.

Now format to Number


Gord Dibben MS Excel MVP

On Wed, 31 Jan 2007 09:38:15 -0800, "Diane Walker"

wrote:

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.

"Sean Timmons" wrote in
message
...
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" wrote:

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.

"Sean Timmons" wrote in
message
...
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" wrote:

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.

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 EditReplace 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

On Thu, 01 Feb 2007 15:50:56 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Thu, 1 Feb 2007 15:15:49 -0800, "Diane Walker" wrote:

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,

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.

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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 EditReplace 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

On Thu, 01 Feb 2007 15:50:56 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

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

On Thu, 1 Feb 2007 15:15:49 -0800, "Diane Walker"
wrote:

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.