Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoSum and other Problems

    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?

  2. #2
    calacuccia
    Guest

    Re: AutoSum and other Problems

    Hi

    Can you attach an example of how you are trying to apply the autosum.

    For your extra question:
    Max. 65536 rows and 256 columns. These are absolute limits.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoSum and other Problems

    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.
    Legare Coleman

  4. #4
    calacuccia
    Guest

    Re: AutoSum and other Problems

    Legare,

    32767 rows is for Excel 98 (Mac version)

    Specs for Excel 2000 in http://support.microsoft.com/support/kb/ar...s/Q264/6/26.ASP

    Excel 97 has identical properties.

    Versions Excel 95/5.0 and prior had 16383 rows.


    Specs for Excel 98 (for the Mac) http://support.microsoft.com/support/kb/ar...s/Q189/5/93.ASP

  5. #5
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoSum and other Problems

    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!
    Attached Files Attached Files

  6. #6
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoSum and other Problems

    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

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: AutoSum and other Problems

    Replace the words "NONE" with zero.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Bob W
    Guest

    Re: AutoSum and other Problems

    [img]/w3timages/icons/smile.gif[/img] The most common reason among my students was when they learned a bad habit of entering a space to clear a cell. The cell is not empty, it contains a space.

  9. #9
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoSum and other Problems

    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.
    Attached Files Attached Files

  10. #10
    calacuccia
    Guest

    Re: AutoSum and other Problems

    Hi,

    The problem is definitely that the cells are not in number format.

    In attachment, I have your book but where the sum is made using:

    =Sum(Value(B2:B22))

    Now it works, because using the value function, the intrinsic value of the cells is used. This is an array formula though and was entered using Ctrl+Shift+Enter.

  11. #11
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoSum and other Problems

    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.

  12. #12
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoSum and other Problems

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

    Jim

  13. #13
    Bob W
    Guest

    Re: AutoSum and other Problems

    [img]/w3timages/icons/smile.gif[/img]The problem is simply that you have some text, or words, entered that you think are values, or numbers. That

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •