follow last post of how to calculate fixed rows, tks

[Guest]

tks a lot for several warm-hearted buddies who have answered my questions of
how to use functions to calculate every 7 rows(a week). but i still don't
fully understand the ROW function,


the formula is: =sum(offset("daily expense sheet"!D$2,(ROW(1:1)-1)*7,0,7,1)

with this, i can drag the formula horizontally and vertically to calculate
the sum of every 7 days(rows)......for every categories.......very nice

but sorry for my ignorance, for the part (ROW(1:1)-1)*7, which is the row
input of Offset function, i do not quite understand the Row(1:1) part till
now.......can some experts pls tell me what does it mean?

lots of tks!

 

[Biff]

Hi!

=sum(offset(D$2,(ROW(1:1)-1)*7,0,7,1)

ROW(1:1)-1) means at which row, starting from cell D2,
to "look" for the values to sum. So, ROW(1)-1 = 0 and 0*7
= 0 so that means offset D2 by 0 rows and the sum starts
with the first value in D2. The Height parameter, 7, means
sum the 7 values starting from cell D2 to D8.

Now, when you drag copy that formula the ROW function will
increment to ROW(2:2), ROW(3:3), etc. So, ROW(2)-1 = 1 and
1*7 = 7 so that means to offset D2 by 7 rows which would
be D9 and sum the 7 values starting from cell D9 to D15.

Hope that helps!

Biff

 

[Aladin Akyurek]

You picked out one that is not robust. Just insert a row before the formula
cell to see...

 

[Biff]

You picked out one that is not robust. Just insert a row

before the formula cell to see...

Yes, but in *his real-time application* will that *EVER*
be something that would actually happen?

There's robust and then there's overkill.

Biff

 

[Aladin Akyurek]

Yes, but in *his real-time application* will that *EVER*
be something that would actually happen?

There's robust and then there's overkill.

[...]

It depends on how you look at it. Many spreadsheet errors is due to formulas
that are not robust against beautifying actions like inserting a row/column
in order to add labels or carry out some additional calculations. When such
formulas do not error out under such actions, the incorrect results might go
unnoticed. Hence it's better to avoid idioms like ROW(1:1), ROWS(1:1), etc.

 

[Aladin Akyurek]

My reply in your original thread was:

=SUM(OFFSET(A$1,(ROW()-ROW($A$1))*7,0,7,1))

The ROW()-ROW($A$1) bit anchors the calculation to the first cell of the
data you want to process.

Suppose that the data to process is on Sheet1 and you need the SUM formula
in E2 on Sheet2...

In E2 enter:

=SUM(OFFSET(Sheet1!A$1,(ROW()-ROW($E$2))*7,0,7,1))

Now the anchoring is to cell E2, the first cell you want to house the
formula, on Sheet2.

tks a lot,for both of you!
now, i get a better understanding.......

and yes, indeed that i have change the formula manually to row(1:1) each
time when i want to insert a row in front of the formula row........
can u further tell me how to avoid or in ur words how to get it more
robust?

tks!

tony

"Aladin Akyurek"??:

You picked out one that is not robust. Just insert a row
before the formula cell to see...

Yes, but in *his real-time application* will that *EVER*
be something that would actually happen?

There's robust and then there's overkill.

[...]

It depends on how you look at it. Many spreadsheet errors is due to
formulas
that are not robust against beautifying actions like inserting a
row/column
in order to add labels or carry out some additional calculations. When
such
formulas do not error out under such actions, the incorrect results might
go
unnoticed. Hence it's better to avoid idioms like ROW(1:1), ROWS(1:1),
etc.