Subtotals Are One Off

  • Thread starter Thread starter Carroll
  • Start date Start date
C

Carroll

I was using the Subtotals feature under data. Does anyone know why my
subtotals would be one off? Here is what the top of my report looks
like:

Adj Level Week Ending Case Type IDA Stus
Electronic Count 66
Paper Count 46
Concurrent Count 113
Electronic Count 61
Paper Count 38
Other Count 100
Electronic Count 33
Paper Count 14
Title II Count 48
Electronic Count 95
Paper Count 44
Title XVI Count 140
1/28/2005 Count 400

66 + 46 = 112, but the total concurrent count is 113. This pattern
holds true throughout. The lowest level subtotal is one less than the
subtotal that is one level above that. Any ideas?

Thanks,

Carroll Rinehart
Baltimore
 
Hi Niek,

Thanks for responding. I read the link, but I don't see that as the
problem. First of all, I am using counts rather than sums, and all of
my numbers could only be whole numbers since I'm counting, so there
really can't be a rounding issue. Any other thoughts?

Thanks,

Carroll Rinehart
 
Several Excel versions ago, there were some issues (fixed by a service
patch) with Excel not recalculating when it should, but I am not not
aware of any issues with Excel's math being wrong. Therefore the
presumption is that your issue is with something that you have done.
Unfortunately you have only given results, not the formulas that
produced them, and we are not mind readers.

Jerry
 
Jerry,

There are no formulas involved. Just the use of the Subtotals utility
under the menu item "Data", where I have subtotals and nested
subtotals. If someone has used this utility where the nested subtotals
(using count rather than sum) do not equal the subtotal above it, I
would be interested in knowing if someone has encountered a situation
where the counts were just one off in every case.

Thanks,

Carroll Rinehart
 
If you look at the cell that contains the subtotal, you'll see that
Data|Subtotals added a formula to that cell.

=subtotal(9,a2:a13)
for example.

If that cell doesn't contain a formula, then maybe someone did a Copy|Paste
Special|Values and destroyed the formulas.

If that happened, try removing the subtotals (an option on that data|subtotals
dialog) and reapply them.
 
When you create the subtotals, in the 'Add subtotal to' section, select
fields that are not the ones being used for grouping.

For example, using the sample data here:

http://www.contextures.com/xlSampleData01.html

sorted by Region and Rep.

If I subtotal on Region (Region, Count, add subtotal to Region) I get a
count of 6 for Alberta
If I then subtotal on Rep (Rep, Count, add subtotal to Rep) I get a
count of 7 for Alberta

However, if I add subtotal to Units, the count is correct
 
The subtotals utility puts in formulas where the subtotals are calculated.
You can look at the subtotals and make sure the ranges being subtotaled are
correct. Select the cell where the number appears and you should see

=subtotal(2,F1:F3) in Cell F4 as an example.

If the range includes another subtotal formula, that should be OK, because
subtotal ignores cells containing subtotal formulas in its range.
 
Thanks a bunch folks! I now see what's happening. First of all, I
didn't realize the subtotal numbers were formulas. I thought they were
hardcoded and part of the same string as the, e.g., "Sorvino Count" or
the "Thompson Count", since I chose to have the count appear these
strings.

Debra, you were right on! First of all, thanks for suggesting the use
of the spreadsheet that was out on the web. That makes good sense to
use one that anyone can access. Debra, you were also correct in that I
shouldn't add the subtotal to the same column that I'm doing the count
on. The formulas do show that they are including all of the subtotal
lines in with the count (except for the very last one that Excel does
in fact exclude). So for Ontario, if I added up all the subcounts for
the reps, I get 24. But Ontario's total shows 29! Quite a difference.

Thank you Niek, Jerry, Dave, Debra And Tom, for taking the time to read
my question and helping me. I hated the thought that I couldn't make
use of this utility due to lack of trust.

Carroll
 
You're welcome. Thanks for following up, to let us know that the
information helped you.
 
Back
Top