Ask Your Question
0

Functions As SUM() Parameters/Arguments? [closed]

asked 2017-04-16 20:22:38 +0100

jseymour gravatar image

I can't count the number of times I've tried to figure this out. Search? I've searched and searched and searched. Finally I must ask.

LibréOffice version is inconsequential. I've had this problem with every version I've ever used.

Given a column you wish to sum(), where rows may be added, why does this not work:

=SUM(D3:ADDRESS(ROW()-1,4,3,1))

If I put

=ADDRESS(ROW()-1,4,3,1)

into, say, cell D35, I'll get "D34". So why does my SUM(), above, not give me the result of =SUM(D3:D34)? Instead I get "Err:502," "Invalid Argument." What am I, or LibréOffice Calc, doing wrong?

Thanks, Jim

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2017-06-08 21:56:51.268531

2 Answers

Sort by » oldest newest most voted
1

answered 2017-04-16 22:37:38 +0100

Lupp gravatar image

updated 2017-04-16 23:30:52 +0100

(In addition)
SUM() accepts constants or References on its parameter positions. Texts given as constants raise an error, texts introduced via references are ignored. Numbers (whether scalar constants, results of scalar expressions, elements of arrays calculated by array expressions, or introduced via references) are added.
The result of an ADDRESS expression is text, not a reference. Since D3 is a reference and there is no operator to combine a reference with text, D3:ADDRESS(Something) cannot be anything meaningful.
In a strange case where you actually need to calculate a reference there are two ways:
1) OFFSET(GivenReference;RowsOffset;ColumnsOffset;NewHeight;NewWidth)
2) INDIRECT(CellAddressOrRangeAddressFormedAsAtext)

Also:
Using =ADDRESS(ROW()-1,4,3,1) you would never get D34, but probably $D34. To get the address relative concerning the column as well you would have to use =ADDRESS(ROW()-1,4,4,1).
To obtain the reference originally pursued INDIRECT("D3:"&ADDRESS(ROW()-1,4,4,1)) was the approppriate expression.

edit flag offensive delete link more

Comments

Thanks, Lupp!

jseymour gravatar imagejseymour ( 2017-04-16 23:56:43 +0100 )edit
0

answered 2017-04-16 22:09:49 +0100

karolus gravatar image

actually nobody knows why the hell you don't use simply =SUM(D3:D34) ???
This Formula expands "automagically" aslong you're insert additional Rows between Rows 3 and 34

edit flag offensive delete link more

Comments

Because new rows were appended to the end of the existing rows. How, odd, no? :)

jseymour gravatar imagejseymour ( 2017-04-16 23:57:22 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2017-04-16 20:22:38 +0100

Seen: 285 times

Last updated: Apr 16 '17