Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Kennett Square, Pennsylvania, USA
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Wrong Subtotals (Excel 2002)

    This is unusual. In a list containing columns Bus (Text), PU Time (date-time), Drop off Time (date-time), Name (text), the list is sorted by Bus, PU Time and the name. The sort works fine. Then subtotals are applied using the "for every change in the bus field..". The problem is that Excel creates subtotals when there is no change in the bus field. There could be two rows then a subtotal, then 1 row - all with the bus name being A1. This problem does not exist is the file is moved into Excel 2003 - in which case subtotalling works as advertised. All updates to Office have been applied. Any help would be appreciated.

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

    Re: Wrong Subtotals (Excel 2002)

    Is it possible that there are differences that are hard to see. Like different numbers of spaces between words, spaces before or after the bus ID? Could you upload the workbook so we can look at it?
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Kennett Square, Pennsylvania, USA
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrong Subtotals (Excel 2002)

    I thought of that as well, but to prove to myself that it was not this, I copied the first cell Bus # into all of the other cells with the same Bus # - and it still happens.

    Bill

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Wrong Subtotals (Excel 2002)

    Could you attach an example sheet that has this odd behavior?

    Steve

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

    Re: Wrong Subtotals (Excel 2002)

    Its going to be tough figuring this out without seeing the workbook. Can you upload an example, with any proprietary data changed of course.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Kennett Square, Pennsylvania, USA
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrong Subtotals (Excel 2002)

    Yes, I can upload a copy of the database. Are you running Excel 2002?. The reason I ask is that this problem does not occur with Excel 2003.

    I will need to make some changes to the proprietary information before I upload the database.

    Bill

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

    Re: Wrong Subtotals (Excel 2002)

    I am running Excel 2000, but there are a lot of others running 2002.
    Legare Coleman

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Wrong Subtotals (Excel 2002)

    I run XL97 and Hans (I think) runs XL2002, so we should have most of the versions covered at least to see if it is a "pre-2003" bug.

    Steve

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wrong Subtotals (Excel 2002)

    Yes, I (Hans) do run Excel 2002 (XP).

  10. #10
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Kennett Square, Pennsylvania, USA
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrong Subtotals (Excel 2002)

    OK, here is a copy of workbook where the subtotals are not working correctly. I did have an opportunity to test this on Excel 2002 and it did replicate the problem exactly as it was doing on the other computer (remember, this does not happen in Excel 2003). The file that I am uploading here shows the inaccurate subtotals. I certainly will be interested in any comments you have and also certainly appreciate your help.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Wrong Subtotals (Excel 2002)

    You need ot put all the same "bus Stops" together. You have "blanks" in the BusStop columns where you have "walkers" and so you have "groupings" of stops with blanks among them giving you additional "changes" to subtotal..

    Remove the filter (data - filter - show all), as desired, add the subtotal, Refilter (walk "does not equal " Y)

    Steve

  12. #12
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Kennett Square, Pennsylvania, USA
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrong Subtotals (Excel 2002)

    The file was posted with the subtotals in place. In order to view the problem, remove the subtotals and then regenerate them using change in bus. In Excel 2002 you'll see that the subtotals are incorrectly applied. This problem does not occur in Excel 2003.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wrong Subtotals (Excel 2002)

    Subtotals will only work correctly if there are no blanks in the column on which you want to "break". This is not a bug or an error, it is the way subtotals were designed. Since - as Steve remarked - you have blanks in the Bus Stop column, Excel will see extra "breaks".

    List processing has been enhanced in Excel 2003 - see for example Woody's Offfice Watch for Mere Mortals vol 5 no 12. I don't have 2003 myself, but this might be the cause of the difference in behavior.

  14. #14
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Kennett Square, Pennsylvania, USA
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wrong Subtotals (Excel 2002)

    Hans -- perhaps you did not see my post above. A quick look at the file does not really indicate that the subtotals are already in place. In order to see the problem, first remove the subtotals and then try to insert them again, do a "for every change in bus", do a count.

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Wrong Subtotals (Excel 2002)

    As I mentioned, and Hans reiterated, and I will mention again. The subtotals are not "incorrectly applied". The subtotals are applied correctly. Your list is "incorrectly sorted" so that all the BusStops are not in a continuous rows: you have blanks within your range. Having them "filtered" does not remove the "blanks".

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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