Re: Subtotal function

["Darin Maxwell" <falcon wt net>]

Here's something else to worry about. The subtotal, sort, and filter
functionality does automatic list detection. If you only have one cell
selected, choosing the sort, subtotal, or filter menu options automatically
chooses the selection based on some rules explained in Excel's help. It
tends to guess right in so many cases that I use this feature extensively.
This should probably be a generic list detection routine since it's used in
so many functions.

Jody Goldberg <jgoldberg@home.com> wrote:
> On Fri, Dec 03, 1999 at 08:44:25PM -0600, Darin Maxwell wrote:
> > I'm looking into adding the subtotal functionality that exists in
Excel's
> > Data menu as it's one that I use often in my work, but I've noticed that
the
> > subtotal worksheet function in Gnumeric isn't working correctly. It
doesn't
> > handle nested subtotals correctly, and the subtotal function's whole
reason
> > for being is to nest. <grin>
>
> You're correct.  This is a bug.  Please submit it to bugs.gnome.org.

I will do so.

> 2) I can't see a non-ambiguous way to implement what this function
supposedly
> offers.  Apparently neither did the XL people.
>
> ie in XL2000
>     A1 : 10
>     B1 : subtotal(9,A1) = 10 ok
>     B2 : subtotal(9,A1) = 10 ok
>     B3 : subtotal(9,B1:B2) = 0 ???

Yes, this is really bizarre. I guess they don't expect people to subtotal
items in other columns, but I do this all the time. To my mind, this is a
bug in Excel. I may go do a search on MS's site to see if they have it
listed as a bug. If not, I'll submit it.

> The documentation is far from comprehensive.  It alludes to 2 type of
> functionality that will need adjustments to the core to support.
>     - ignoring nested subtotals.
>     - ignoring hidden rows/cols. (when we support hiding)

Excel doesn't ignore hidden rows/cols for sorts and subtotals; that would
defeat the purpose for subtotals. I think that may be intended for filtered
data sets. (?)

> However it does not explain how to handle nested subtotals of
> different types
> ie a subtotal(8, ???) that contains a subtotal(9, xxxx)

I know the answer to that one. I've done multiple level subtotals where I
subtotal the same column by summing and by counting. The numbers come out
right, so it almost certainly ignores subtotals which aren't of the same
type.

However, I can think of at least one use for this functionality. What if I
wanted to choose the sum of the maximums from a data set? I.e. the max of
{1,2,3} plus the max of {4,5,6}. You can't just do a subtotal(9,) on the
range, that'll give you the sum of {1,2,3,4,5,6} since it ignores the
subtotal(4,)'s. Maybe you have to do a filter on the data set first to
choose the maximums?

> As a first step I'll make a request.  Can someone who has used this
function
> extensively and is familiar with XL implementation write a large detailed
> comment (some documentation extensions wouldn't hurt either) detailing the
> behavior in these cases ?  (Darin maybe ??)

I'll give it a shot. I actually did make an attempt at creating the subtotal
menu item, but was stymied by the subtotal worksheet function's
implementation problems. I had a working implementation but accidentally
deleted it while upgrading to vers. 0.45. Sigh.


Darin Maxwell
falcon@wt.net