AutoSum and other Problems

Topic

New Reply

What would cause and Excel Spreadsheet (Excel 2000 SR-1) to not AutoSum correctly? The only way I can get it to work is to manually retype in the numbers. I have tried reformatting the cells differently to see if it would have any effect but it doesn’t. I also am finding Spreadsheets that won’t sort correctly either, again unless the data contained in the cells get re-entered.Thanks, I searched through all 15 pages of posts and couldn’t find anyone else with the same problem but I hope someone knows the cure.
As a bonus question is there a limit to the number of Rows I can put in an Excel Spreadsheet?

Reply | Quote

Replies

Most likely there is something in the cells that makes Excel think that the values are text not numbers. It could be something like spaces before the first digit or some other non-display character. When you retype the number, you are removing whatever is causing the problem. How did the data in the cells get there in the first place? Was the data imported from a file, copied and pasted from somewhere like a web page, or some other source that might have included some non numeric characters? This could also cause things to not sort correctly. Another possibility is that the cells are formatted as text.

The maximum number of rows for Excel 2000 is 32,767. For Excel 95 and earlier it is 16,383. I’m not sure which it is for Excel 97, but it is one of those two.

Reply | Quote

Yes, here is an example of one of the files attached. Columns B and C won’t autosum or sort correctly. I wasn’t sure that attachments were ok or I would have attached it earlier. Thanks!

Reply | Quote

The data was entered by the user who made the spreadsheet, no data was imported. I’m not sure when formatting was first applied to the cells, but didn’t think that would make a difference.

Jim

Reply | Quote

Replace the words “NONE” with zero.

Reply | Quote

I made that change and it had no effect, it was the first thing I thought of also, replacing None with 0. I should have attached that version, sorry. Here is the corrected version, and as you can see it had no effect.

Reply | Quote

If the cell formatting was ‘Text’ when the numbers were entered, and you change it to ‘Number’, the data attributes remain text. You can either re-enter all the numeric data or do a Copy / Paste Special – Multiply on that range. Just enter the number “1” in an unused cell, Copy that cell, then do Paste Special – Multiply on your data range. This multiplies all the data by 1 and changes the data attribute to numeric if it’s a number.

Reply | Quote

Thanks to all of you for your help! Your great!

Jim

Reply | Quote