SUBTOTALS misbehaving when rows are grouped - stumped!

[Coltrane59]

I have a workbook with a column C that includes several amounts and subtotals.

I also group the rows within the worksheet so that I can either show all rows (level II grouping), or collapse to level I grouping to only show rows with a value greater than $1 million or less than $(1) million. That is, I group into a single level any rows that are between $(1)M and $1M.

OK, now for the problem. If I expand all groupings to show all rows, my subtotals do what they're supposed to and recalculate to include all the visible rows. BUT, when I collapse to Level I (to only show the rows with amounts above the +/- 1 million threshold), some of the subtotals, but not all, don't quite calculate correctly. For example, they'll show $(14.4), when the three items above it are $(14.3), $(1.5), and $1.2; this should = $(14.6).

Now, if I highlight the subtotal cell with the calculation error, Press F2 (to put the cell in edit mode), and press 'Enter', the calculation updates correctly.


However, as soon as I expand my groupings and collapse them again, the calculation goes back to $(14.4).


Note, that the sum of all the items in the expanded group (all rows, level II), is $(15.3), and the SUBTOTAL function correctly calculates that when my groups are expanded.

I have no idea where the $(14.4) is coming from, or why I have to manually F2+Enter the cell in order to fix the calculation.


Any ideas??

[zbor]

Go Format cells, format as number and put more numbers after decimal places.

When you summ all of them you'll get your result

[Coltrane59]

Go Format cells, format as number and put more numbers after decimal places.

When you summ all of them you'll get your result

Thanks for the suggestion - I tried this, but doesn't solve it in my case. I need to show the numbers in millions, and they're all rounded to the hundred thousand (using ROUND function). So, for example, $1,203,671 would show up as $1.2. Expanding decimals would show $1.200000...

We show it in millions by using a custom format for the numbers (instead of dividing everything by a million -- the workbook is older than me):

_($* #,##0.0,,_);_($* (#,##0.0,,);_($* "-"_);_(@_)

I could get the same result by going through each and every formula in the sheet and dividing by a million, but that's a lot of manual work.

[Coltrane59]

One thing I just noticed while playing with things a bit is:

My subtotals line is immediately below the rows it is evaluating above. The issue happens when I have grouped the bottom rows of a subtotal group. For example (numbers indicate example row #):

1 - grouped
2 - not grouped
3 - not grouped
4 - not grouped
5 - grouped
6 - grouped
7 - SUBTOTAL (rows 1-6)

When I collapse the grouped items, the subtotal function seems to not catch row #5 and #6, the last set of grouped rows immediately before the Subtotal.


I just found that I can fix this by adding an additional blank row before the Subtotal, and including that row in the Subtotal function's range. For example:

1 - grouped
2 - not grouped
3 - not grouped
4 - not grouped
5 - grouped
6 - grouped
7 - blank row
8 - SUBTOTAL (rows 1-7)

If I don't include row 7 in the subtotal, the same problem I initially experienced occurs.

[Coltrane59]

Also, I should note, that my subtotals work if the final rows of a subtotal range are not grouped, for example:

1 - grouped
2 - not grouped
3 - not grouped
4 - not grouped
5 - grouped
6 - not grouped
7 - SUBTOTAL (rows 1-6)


My subtotals function_num is 109, which is a SUM that excludes hidden/non-visible rows.

[Coltrane59]

I've replicated my issue with a simple blank excel worksheet, using simple 'Number' formatted numbers, just to be sure this isn't the issue.

Here's how to replicate it:
1) Open new workbook
2) In cells A1:A4, enter the numbers 1, 2, 3, and 4
3) In cell A5, enter formula =SUBTOTAL(109,A1:A4)

Note that the subtotal works, and totals to 10.
4) Now group row 2 and 4.
5) Collapse groups to level I (to hide row 2 and 4)

The result is 8 (i.e., 1 + 3 = 8), which is obviously wrong.


You can update the subtotal, by selecting it, hitting F2, then Enter. It then correctly updates to a subtotal of 4.

As I noted above, this can be solved by adding a blank row (row 5) and updating the SUBTOTAL function, now in row 6, to =SUBTOTAL(109,A1:A5)



I don't understand why this is. And, I don't really want the blank rows. Is there another solution to this issue?

Thanks!