Results 1 to 15 of 21
Thread: Wrong Subtotals (Excel 2002)

20041111, 17:40 #1
 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 (datetime), Drop off Time (datetime), 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.

20041111, 17:56 #2
 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

20041112, 15:15 #3
 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

20041112, 16:07 #4
 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

20041112, 17:52 #5
 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

20041112, 18:35 #6
 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

20041112, 20:38 #7
 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

20041112, 20:43 #8
 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 "pre2003" bug.
Steve

20041113, 15:24 #9
 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).

20041114, 19:41 #10
 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.

20041114, 21:42 #11
 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

20041115, 13:13 #12
 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.

20041115, 13:28 #13
 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.

20041115, 13:55 #14
 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.

20041115, 13:55 #15
 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