Running total as header formula

Question

Level 6

17,794 points

Running total as header formula

I am looking for a simpler, more general, or just plain better way to calculate a running total of a column of numbers, such that the running total cell is the sum of a number in the same row & a different column plus the cell just above it in the same column.

IOW, if the numbers column is H & the running total column is J, I could use =H5 + J4 in cell J5. In J6, I could use =H6 + J5, & so on. I can extend the formula downward without any problems, but I must make an exception for the first row of data: for instance, if row 2 is the first to have a number to include in the running total, then in J2 I must use =H2, since J1 is a header column.

This means I can't use a simple formula in a header column for the running total. (I want to do this because I will be adding rows as the table is used, & I want all the formulas to automatically be added to each new row.)

After trying a lot of functions, I finally came up with this one for the header formula:

=H + INDIRECT(ADDRESS(ROW()-1,COLUMN(),4))

It works, but I suspect there is an easier, more elegant, or more general formula that (for instance) won't break if I rearrange the columns in the spreadsheet.

Any comments would be appreciated.

iMac G5/2.0 GHz 17" ALS; White MacBook/2.4 GHz, Mac OS X (10.5.2), Kensington Trackball

Posted on Jun 25, 2008 7:42 AM

Reply

Answer 1

KOENIG Yvan

Level 8

41,825 points

Jun 25, 2008 9:39 AM in response to R C-R

Hello
This one works even if row 1 is a header one.

=IF(ISBLANK(H),"",IF(ISERROR(H INDIRECT(ADDRESS(ROW()-1,COLUMN()))),H,HINDIRECT(ADDRESS(ROW()-1,COLUMN()))))

Of course, if row1 is a header, entering it in this row will fill the entire column.

It will survive if you insert columns before column H or between columns H and the one where is the formula.

If the running total column is not adjacent to column H, you will also be able to remove the intermediate columns.

If you are patient, you may use the fact than <aString>*1 generates an error to take care of a possible string in a cell of column H.

I apologize but I am asked on the phone.

Yvan KOENIG (from FRANCE mercredi 25 juin 2008 18:39:17)

Reply

Answer 2

KOENIG Yvan

Level 8

41,825 points

Jun 25, 2008 12:42 PM in response to KOENIG Yvan

Here is an other version which take care of non numeric values contained in the column "values".

=IF(OR(ISBLANK(H),ISERROR(H*2)),IF(ROW()=1,"",INDIRECT(ADDRESS(ROW()-1,COLUMN()) )),H+INDIRECT(ADDRESS(ROW()-1,COLUMN())))

When I want to insert this kind of formula in a header row, I uses this kind of script:

set formule to "=IF(OR(ISBLANK(H),ISERROR(H*2)),IF(ROW()=1," & quote & quote & ",INDIRECT(ADDRESS(ROW()-1,COLUMN()))),H+INDIRECT(ADDRESS(ROW()-1,COLUMN())))"
set theApp to "Numbers"
tell application theApp to activate
tell application "System Events" to tell (first process whose title is theApp)
keystroke formule & return
end tell

because pating a formula gives an odd result.

Yvan KOENIG (from FRANCE mercredi 25 juin 2008 21:39:37)

Reply

Answer 3

R C-R Author

Level 6

17,794 points

Jun 26, 2008 6:50 AM in response to KOENIG Yvan

Yvan,

Thank you for your comments, & for the formulas. I was hoping there was a simpler way to do this than with INDIRECT & ADDRESS, but apparently not.

In this particular spreadsheet, I do not need to worry about any blank values in column H because they are the results of a calculation that yields a number even if the row is otherwise blank.

The ISERROR clause does allow me to name the running total column since without it the formula would be trying to add a string to a number in row two. However, in this particular spreadsheet I do not need to name that column because it is hidden. (The running total number column is used in another column that formats it as part of a string that contains other conditional information.) In the interests of simplicity, I will not add the ISERROR clause to my formula in this spreadsheet but I will keep it in mind for use in other spreadsheets in which the column will not be hidden & needs to be named.

Of course, as an alternative I could just create the column name as an independent text object. Wherever possible, I tend to favor workarounds like that to reduce formula complexity.

Reply

Answer 4

KOENIG Yvan

Level 8

41,825 points

Jun 26, 2008 7:41 AM in response to KOENIG Yvan

Don't worry

I took the opportunity to show the way we may use ISERROR to get rid of some special cases.

ISERROR(1/H) is an efficient way to identify the fact that H is empty or contains a string.

I wished also to show how we may use exactly the same formula in row 1 and in other rows.

Knowing that we may do that doesn't forces us to use this kind of formula.

If you don't need to be able to sort rows, you may use the simple:

=IF(ISERROR(1/F1),E,E+F1)

Yvan KOENIG (from FRANCE jeudi 26 juin 2008 16:41:28)

Reply

Answer 5

R C-R Author

Level 6

17,794 points

Jun 26, 2008 8:31 AM in response to KOENIG Yvan

KOENIG Yvan wrote:
If you don't need to be able to sort rows, you may use the simple:

=IF(ISERROR(1/F1),E,E+F1)

Perhaps I am missing something, but I do not see how to make this into a header formula that works for the entire F column. (All the cells refer to F1.)

My first approach was just to set F2 to =E2, then set F3 to E+F2, & extend that formula down the column as needed. It is very simple & straightforward, but as I mentioned, I want a header formula so that newly created rows will automatically have the appropriate formula.

That requirement is what led to the indirect address formula.

Reply

Answer 6

KOENIG Yvan

Level 8

41,825 points

Jun 26, 2008 8:50 AM in response to R C-R

In the flow I forgot that the formula was to be entered in the header.
With this request, we can't get rid of the INDIRECT(ADDRESS(…) addressing.

Yvan KOENIG (from FRANCE jeudi 26 juin 2008 17:50:29)

Reply

Answer 7

Kyd

Level 1

38 points

Jun 26, 2008 8:58 AM in response to R C-R

I'm sure that I'm missing something, but could you create two tables? Make one table contain all of your data. Put the other table right above it and make it have just one row. Put your totals in that table.

Reply

Answer 8

KOENIG Yvan

Level 8

41,825 points

Jun 26, 2008 9:27 AM in response to Kyd

Kyd wrote:
I'm sure that I'm missing something, but could you create two tables? Make one table contain all of your data. Put the other table right above it and make it have just one row. Put your totals in that table.

As far as I know, this would give a total, not a running total 😉

Yvan KOENIG (from FRANCE jeudi 26 juin 2008 18:27:03)

Reply

Answer 9

R C-R Author

Level 6

17,794 points

Jun 26, 2008 9:45 AM in response to KOENIG Yvan

KOENIG Yvan wrote:
With this request, we can't get rid of the INDIRECT(ADDRESS(…) addressing.

Thank you. This well answers my question about the possibility of there being a better or simpler way than having to use a formula as complex as this one in a header formula. I'm not quite sure what I was hoping for, but you have confirmed my suspicions that there probably is none, at least when the header formula contains a relative reference to one of its cells.

Reply

Answer 10

KOENIG Yvan

Level 8

41,825 points

Jun 26, 2008 11:33 AM in response to R C-R

While we are writing upon header cells I wish to know your (I ask to every poster reading this thread) advice.

When we paste a formula copied from a standard cell or from a WP tool into an header cell, the result is not an active formula filling the entire column, it is a text item.

Is it a logical behavior?
From my point of view it's odd but maybe I miss one or several aspects of the problem.

Yvan KOENIG (from FRANCE jeudi 26 juin 2008 20:31:22)

Reply

Answer 11

R C-R Author

Level 6

17,794 points

Jun 26, 2008 12:38 PM in response to KOENIG Yvan

I can not duplicate this behavior. Can you post an example of a formula for which this happens?

Reply

Answer 12

pw1840

Level 3

860 points

Jun 26, 2008 5:49 PM in response to R C-R

R C-R & Yvan,

This behavior seems to occur when a column header has previously had text entered into it. Then it doesn't matter whether or not the old text has been deleted, copying and pasting a formula into that cell results in the formula appearing as new text. Only typing the formula into that cell seems to restore the expected behavior.

pw

Message was edited by: pw1840

Reply

Answer 13

R C-R Author

Level 6

17,794 points

Jun 26, 2008 10:43 PM in response to pw1840

I cannot duplicate this behavior either. Whether or not the header cell has previously had text in it, deleted or not, pasting a formula into the header cell works correctly for me -- it is interpreted as a formula, not as text.

Is it possible that what you see is the result of the header cell format being preset to the text format? (That doesn't seem likely, but I had to ask.)

Also, for Yvan anyway, I think he mentioned in some post that his copy of Numbers had been modified to accept the correct (& here I have to take his word for it) French accents in function names. I wonder if this has anything to do with it?

The only other thing I can think of is that the source of the paste (what the formula is copied to the clipboard from) has something to do with it. I've tried pasting formulas from Safari (from this thread, in fact), from TextEdit, & from Tex-Edit Plus. All work fine -- in fact, line breaks are ignored, which I found a bit of a pleasant surprise. I don't own Word & I haven't used Pages for this test so I can't say if they have anything to do with it.

Reply

Answer 14

KOENIG Yvan

Level 8

41,825 points

Jun 27, 2008 2:26 AM in response to R C-R

R C-R wrote:
Also, for Yvan anyway, I think he mentioned in some post that his copy of Numbers had been modified to accept the correct (& here I have to take his word for it) French accents in function names. I wonder if this has anything to do with it?

Before posting about this kind of behavior, I took care to check with the standard Numbers in French and in English.
It always behave the same.

Yvan KOENIG (from FRANCE vendredi 27 juin 2008 11:26:14)

Reply

Answer 15

KOENIG Yvan

Level 8

41,825 points

Jun 27, 2008 2:31 AM in response to KOENIG Yvan

Back to the original question.
I found a simple formula which may be entered in a header cell.

=$E$1+E

Enter it in F1 for instance.

I am reluctant to this kind of formula which calculate the sum of every"higher" cells but I'm not sure that it would be less efficient than the one using INDIRECT(ADDRESS(…)).

Yvan KOENIG (from FRANCE vendredi 27 juin 2008 11:31:08)

Reply