[Solved] Embedding ROW() in another function

Discuss the spreadsheet application
Post Reply
PapaHuili
Posts: 1
Joined: Thu Feb 15, 2018 7:39 am

[Solved] Embedding ROW() in another function

Post by PapaHuili »

There is a "Tally Row" on a sheet that may be repeated many times. The first element of the row contains the row number at the beginning of the tally range. For instance, the range will be rows 11 through 14 and the tally row will be 15. A15 will contain 11. Here is a function to sum column G11 through G14

=SUM(INDIRECT("G"&A15):G14)

What I want to do is replace the "hard coded" 15 with the ROW() function but can't find the correct syntax.

UPDATE: I've attached the file EEM.ods. Rows 9, 19, and 27 are "tally rows". A9, A19, and A27 contain the row number where the tallies for that particular block begin. I would like the tally row's formulas to be completely generic, that is referring only to the row's first column and everything else relative to the ROW() so that I can copy and paste it without have to manually modify it afterward as I create new tally blocks.

Note the each tally block may contain an indeterminate number of rows according to the number of trades necessary to declare that sequence either a winner or a loser (denoted by either a red or green bar), or still pending (a gold bar).

SOLUTION: there may be a cleaner way but this works:
=SUM(INDIRECT("G"&INDIRECT(ADDRESS(ROW(),1,4))):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4)))

Thank you to all who responded.
Attachments
EEM.ods
(15.32 KiB) Downloaded 68 times
Last edited by PapaHuili on Thu Feb 15, 2018 10:19 pm, edited 2 times in total.
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: embedding ROW() in another function

Post by keme »

=SUM(INDIRECT("G"&ROW()):G14)
... or if the bottom row is row 14, perhaps ...
=SUM(INDIRECT("G"&ROW()):$G$14)
The second one will always stop on row 14, while the first will alter ending row and/or column when copied to other cells.

Using the OFFSET() function is often a better solution than the more intuitive INDIRECT() approach. What exactly do you want to achieve, and how is your data arranged? Could you attach your file (or an excerpt from it) to a posting here?
Remember that this is a public place, so nothing confidential!
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: embedding ROW() in another function

Post by Villeroy »

Why not work with mixed references?
X1: =SUM($G1:$G$14) references the range in column $G between this row (where X1 is) and absolute $G$14

With OFFSET:
=SUM(OFFSET($G$14; ROW()-14 ; 0 ; 14-ROW()+1 ; 1))
OFFSET is way more powerfull because it can calculates ranges of any size from any source ref (single cell $G$14 in this case) any amount of rows up or down, any amount of columns to left or right and resized to any amount of rows and columns.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply