IFERROR vs. Subtotal

Question

Level 1

1 points

IFERROR vs. Subtotal

Hello!

This is the way I’ve configured a column: the top-first cell N4 with =SUM(J4;L4) and the next cells like N18 with =IFERROR(J18+L18+N17;0) in order to get a cumulated value there.

From the point of view of the cell only, it appears the formula works correctly.

But there is a kind of conflict with the subtotal lines – e. g. N19, when I pull the formula from N18 down to N20 and further.

The regular N-cells display a cumulated value based on the value of the vertical-prior cell, but I’d like to exclude from this sum the cells showing a Subtotal. (N19, N21, N23, N25)

The problem: when I pull the right corner of any N-Cell down along the colomn, it considers the subtotal cells as a regular one. The N20 cell uses N19 instead of N18.

It would be a mess if I always have to double-check the whole column, as lots of new row will be created during the daily use.

How do I get rid of that?

I’m presuming one thing but not really sure if it’s an issue: maybe the formula =IFERROR(J18+L18+N17;0) has to be extended so it does not take the Subtotal cells into account. Right now the formula for N20 should probably be changed manually into =SUM(J20;L20) in order to “open the array”. This would turn the cumulated values into a array-specific ones instead of being global (here, I mean a sheet-wide relevance).

The original idea and still the optimum would be to keep the cumulated N-Column-Values relevant for the whole file, and only the Subtotal-Cells would remain category-specific.

Any hints would be much appreciated

🙂

G

ADDRESSBOOK-OTHER, Mac OS X (10.7.5)

Posted on Dec 17, 2013 4:01 PM

Reply

Answer 1

Best reply

Barry

Level 9

59,867 points

Dec 18, 2013 12:52 AM in response to gestyle

Hi G,

Uncheck the Insert Categories... checkbox in the Reorganize pane.

Select cell N3, containing the formula =IFERROR(J3+L3+N2;0)

Fill down.

Check the Insert Categories checkbox.

Done.

Turn off Insert Categories anytime you are adding rows.

Regards,

Barry

Reply

Answer 2

gestyle Author

Level 1

1 points

Dec 18, 2013 10:00 AM in response to Barry

Thank you Barry!

The Subtotals gather data-entries by Month (M) via Column C – that’s perfect.

But at the same time, each Subtotal is an addition of the cells contained in the array. I mean, N3 is doing =SUM(N4:N18) in other words adding all these cells instead of just displaying the result of the last one N18, which already contains the cumulated sum of the cells – as every regular N-cell is partly defined by the cell above itself ( e.g.: N18 =IFERROR(J18+L18+N17;0))

It seems to bring up two different interpretations of what can be called a “Subtotal”. Apparently, none of the standard build-in functions solve the thing I’m trying to achieve (Displaying the cumulated delta for the considered period/month).

If the amount of rows was static, writing a formula would be easier, but as some rows can be added anytime during the daily use, formatting the Subtotals requires to take this variable into account. I presume one has to do some sort of delta, I mean calling the uplast cell of any Subtotal row (For N19 that would be N18) and not till then substract this value to the value of the last cell in the following array/month/category (For N19, that would be N20, therefor =SUM(N20-N18), but with the variable thing on top.

In the actual state of the table, N19 would display 0,70 because only J20 happened during that period.

But as soon as other entries / rows with values greater than 0 are added, the delta would increase and appear in the Subtotal cell of the array/month/category.

Does that make sense?

🙂

G

Message was edited by: gestyle | …displaying the cumulated delta (instead of the cumulated sum) at the end of the period/month. …

Message was edited by: gestyle | …displaying the cumulated delta (instead of the cumulated sum) for the considered period/month. …

Reply

Answer 3

Barry

Level 9

59,867 points

Dec 18, 2013 11:13 AM in response to gestyle

Hi G,

The functions in Categories work under the assumption that the data in the column being summarized each 'belongs' to the row in which it appears. Considering that assumption, the subtotal in N19 is correct—it is the total of all of the column N entries in the category (ie. equal to the entry in N20.

Taking a subtotal in the category rows of columns J and L will give the results you want (for thse columns), but those separate results cannot be combined as, like all category row calculations, they are for display only, and can't be used in further calculations.

For amonthly VAT subtotal using Categories, you'll need something similar to this table (from your other thread connected with the same tables):

Here, where all the individual VAT amounts are in a single column, a category row subtotal on column D would return the correct subtotal for that category.

Regards,

Barry

Reply

Answer 4

gestyle Author

Level 1

1 points

Dec 18, 2013 12:04 PM in response to Barry

Hey Barry,

In other words, it is not possible to replace the standard-Subtotal function of the N-Cells displaying Subtotals by something like =SUM(of_Value_of_the_last_cell_in_this_category_MINUS_Value_of_the_first_cell_in_this_category), right?

Instead of that, I add one column in order to go and fetch the single Tax-amounts with =SUM(H+J+L). The Subtotal of each month/category will display the correct Taxes-only amount (here in O):

…while I turned off the Subtotal in the Cumulated Taxes Column "N”. I think it makes more sense now.

I renamed the original sheet into “DINSTINCT COLUMNS”.
I created a copy of the original sheet and named it “SWITCH” in order to go ahead with the implementation of the 3 IF-Formulas (those from the IFERROR vs. Subtotal Post). I’ll go ahead and insert them here into "SWITCH”. As a consequence, I’ll have to update the formula in O.

Thank you!

🙂

Reply

Answer 5

gestyle Author

Level 1

1 points

Dec 18, 2013 12:09 PM in response to gestyle

Well, actually, it’s for example O4 =SUM(H4+J4+L4) and so on down the road:

Reply