I have a couple of lines of data with all numeric values entered. And the sum calculated at the end of the row.
I was present when the orginal values were entered and the sum was calculated.
The spreadsheet was sent to the project manager, and he copied the data to his master spreadsheet. All of a sudden the sums were different.

I copied the lines to a new spreadsheet to dig deeper. I first made sure to convert all the cell types to number. That still didn't fix the issue.
Next, I used the Type(cell) formula, and it shows some cells are text. I once again converted these cells to number, and they still don't fix the issue. I looked at the cells, and they're aren't being typecast to text with the ', and they don't contain any formulas. They are just numbers manually entered.
I would say it's an issue with the specific columns, but the rows have different columns that re text vs number.
I've tried copying and pasting values only from the original, and the issue persists. I've tried copying and pasting the formats and still the issue remains. They only thing that seems to work, is manually retyping the numbers, or copying, and pasting directly from another cell.

Any thoughts on this? I would hate to have to manually fix each cell, due to the volume of data.

I would try the Excel format paint tool. Double click a cell of known correct formatting. Then select a range of cells that contain suspect values.

Are the results different if you use A1+B1+C1 etc instead of Sum(A1:c1)?

so when you change the format of a cell, you have to make an entry into it.

In school, they taught us the good ol' text to columns trick where you didn't actually add any columns (delimit with a : or something else that is obviously not in the data set).

Also, for all of you excel jockeys out there, may I suggest buying and downloading the coolest little excel add-in ever... ASAP Utilities. You can't imagine the little excel hacks this thing can do for $45.

Is there a formula or a referenced cell in there somewhere?

Also, the concept of doing copy paste from one spreadsheet to another as part of a business process makes me shudder for exactly this reason...

Are you using excel for tracking project dates? Sounds that way if you're sending them to the PM for updating your tasks.

It might help if you shared what type of information is being tracked.

I've had similar problems when columns are hidden. Any hidden columns in either spreadsheet that may have additional numbers being summed?

That was the first thing I tried, and the cell stayed as text.
The results are the same.

quote:

---

so when you change the format of a cell, you have to make an entry into it.

---

Not sure I follow what you're saying. There is already a numeric value in the cell.

quote:

---

Is there a formula or a referenced cell in there somewhere?

Also, the concept of doing copy paste from one spreadsheet to another as part of a business process makes me shudder for exactly this reason...

---

Already checked for formulas and if there were quotes, and nope. As I mentioned, I watched as the values were manually entered into the first sheet.

quote:

---

Are you using excel for tracking project dates? Sounds that way if you're sending them to the PM for updating your tasks.

It might help if you shared what type of information is being tracked.

---

No it's not being used for tracking dates, it's being used to just collect a bunch of estimates for different teams broken down by month.

It's not to update tasks.

quote:

---

I've had similar problems when columns are hidden. Any hidden columns in either spreadsheet that may have additional numbers being summed?

---

no hidden columns. Like I said in the original post, for some reason some of the columns are of type text instead of numeric, and there is no reason for this. When I try to change the cell format it still stays as text, unless I manually re-enter the value, or copy and paste from a cell that is of type numeric.

I think some people are getting caught up on the wrong stuff, and not reading the original post completely.

The PM sent a blank spreadsheet to us.
We entered our estimates per month in each column, and summed it up at the end. Then sent it back to the PM.
He copied the estimates to his master sheet, and the totals were off.

To figure out why, I took his sheet and started looking at it, and trying
various things to make the sum correct. The only thing that worked was
to manually reenter the data directly.

I used the =Type(cellvalue) formula to determine if the cells were numeric. I found several to be numeric, and a few to be text. It made no sense why. I tried to change them all to numeric, and it still didn't fix it. I tried pasting the format, and that didn't work.

The only two things that worked were:
1) manually entering the data in each offending cell - time consuming
2) copying from a good cell and pasting to a bad cell - time consuming because the values vaired from cell to cell.

If a cell is already in text format, typing a number in wont make it a number format.

The cell format has to be changed. This cannot be done by just format pasting.

To format the cells into number, select all cell and do a "text to column" exercise. You won't actually be adding any columns.

When it asks, select "delimited by:" and enter a character you know is not in the data. Usually something like a tilde or semicolon. Then it will ask you format, select number.

By choosing a character that isn't present, you won't actually split the data into new columns. Instead, the existing columns will now be formatted correctly.

You can also Google this process for pics. Try something like "text to column number formatting excel"

http://www.accountingweb.com/article/three-ways-convert-text-based-numbers-values/222008

scroll about 1/3 way down to "The easiest way to convert a range of values to text, though, involves using the Text to Columns feature"

quote:

---

If a cell is already in text format, typing a number in wont make it a number format.

The cell format has to be changed. This cannot be done by just format pasting.

---

I realize that, and I tried changing the format directly which did not work. However, just simply retyping the numbers did.

quote:

---

To format the cells into number, select all cell and do a "text to column" exercise. You won't actually be adding any columns.

When it asks, select "delimited by:" and enter a character you know is not in the data. Usually something like a tilde or semicolon. Then it will ask you format, select number.

By choosing a character that isn't present, you won't actually split the data into new columns. Instead, the existing columns will now be formatted correctly.

---

Didn't work. But what it did do when the convert text dialog box came up was show there were hidden characters in the fields. They weren't spaces, so I'm assuming they were tabs, inserted via copying and pasting in email or some other method. Unfortunately, it's not allowing me to do a global search and replace, but at least, now I know what the problem is.

ah, so there were phantom tabs in there. gotcha.

I excel jockey for about 25% of my daily hours at work and I recently came across this really cool excel add-in called ASAP Utilities. It's $45 for a life-time (or annual license) and it has multiple excel hack macros.

It would have easily found that and has other really cool functions, i.e., add a formula to multiple cells (show numbers in thousands, or negatives, or whatever)

fill cells with the cell above it (great for pivoting)
remove redundant columns and shift all text to the far left column (helps with filtering and removing duplications/vlookups).

center text without merging cells, removing characters from left or right.

Things that can be done with formulas or macros in an easy interface.

give it a look.