Calc: semi-open cell range?

English
, ,
#1

Here is a challenge: first line contains title of column, i.e. decoration text which is not processed; rows 2 to n contain numbers and row n+1 is the sum of the preceding numbers as e.g. in column b, row 51 =SUM(B$2,B50). Note the mixture of relative and absolute references which allows to insert new rows without the need to modify the formula.

I’d like to have the sum immediately below the title so that row 1 shows the title, row 2 the sum and rows 3+ are the data. In this variant, the first two rows can be frozen and the sums are always visible when you scroll across the data. For convenience, the sum formula should not need to be modified when new data rows are added. Two workarounds are available for that.

  1. Closed reference: use an arbitrarily large limit like =SUM(B$2,B99999) (other answers on this site give an even larger limit, probably equal to the internal bound, but the exact value is not an issue)
  2. Open reference: put the raw data in every other row without any “external” information, moving title and sum to the preceding column. Sum in A2 becomes =SUM(B:B) which is more elegant, but layout turns queer

Is there a way to specify a semi-open reference like (the erroneous) =SUM(B2:B)?

This notation is not accepted by LO and ends up with #NAME? because of B

Is there a way to use ranges with an open end?
#2

The answer hinted by @Lupp is to create a flag column left void for “active” rows and containing some character for discarded rows. Formula =SUMPRODUCT(B:B;A:A="") where B is the column to sum and A the flag one.

#3

Sorry. I cannot recognise either my answer nor any essence from my comments in the comment above. Any visitor to this thread should feel encouraged to read my answr and everything themselves.

#4

You may use AGGREGATE function that ignores AGGREGATEs including itself:

=AGGREGATE(9;0;B:B)

Yes, I understand that this isn’t answering your direct question, just tries to solve your stated challenge.

There’s no way to use “semi-open ranges” directly; one way would be to use stated Calc limits, like in SUM(B3:B1048576), where 1048576 is the largest row possible ATM; this works correctly with regards to row insertions and deletions, but that would possibly need to be corrected in the case of (possible) change of those stated limits.

#5

Thanks for pointing me to AGGREGATE, but it effectively does not answer directly. Nevertheless, the function I did not know is interesting.

#6

To compute SUM above the numeric data is something I would always prefer (except, probably, when working with a pencil on a sheet of paper). To disambiguate things I would, however, use one header row more: A label like “Total”, then SumResult (fomula), then a label like “Amounts”.

To get automatic adaption of the range for summing there are (at least) 3 ways.

  1. Dedicate one empty row below the data to remain empty but included in the RangeToSum. You then can always insert rows above this “dummy” which will automatically be included. Error-prone.

  2. Enable the option 'Tools'>'Options'>'LibreOffice Calc'>'General'>'Expand references when new columns/rows are inserted' and insert additional rows immediately below the current data. Errors in different contexts expected.

  3. Maintain a few META data about your table of data, resulting at least in a counted ActualNumberOfUsedContiguousRows. Base your ranges on this result then using OFFSET (or in specific cases probably ADDRESS and INDIRECT).
    See this demo concerning 3).

“Semiopen” (doubtable term) or “partly absolute” is not actually relevant in the context. Also absolute range references are expanded automatically if one of the ways 1), 2) is gone.

#7

“Semiopen” is a clumsy word referring to maths, hinting that the bound (here “infinite” vs. finite) is included or not in the range.

There may be a simpler solution with sheet tabs: one of them contains the “amounts” and another one is the summary one where I can use =SUM(b:b). However, I’m in trouble when I try to add a heading row. I thought about =SUMIF(a:a;"^$";b:b) flagging the “decoration” rows with anything, e.g. * or 1, in column A, but the regexp does not match on empty cells.

#8

Other comparison criteria like "<>""""" do not work as well. "ISBLANK()" is also incorrect because there is no argument to point to the current cell (and it is no comparison either). How can I match on empty cells so that I only flag the few comment rows?

Note: this is academic because I could =SUM(b2:b104...).

#9

I must have missed something. As I read it, the OriginalQuestion did not contain anything about conditions or ‘Criteria’. Except for the “semiopen” (which I knew from math) I also do not understand clearly your first comment here. Would you mind to explain the issue, whether academic or pragmatic?
Is it about the difference between having empty text returned by a formula and being blank?
Is it about how to get a criterion accepting exactly ‘EmptyOrBlank’?
(Did you study the attached demo?)

#10

Apologies, I was a bit fast: I explored another venue to see how I could avoid the issue of “demi-open” range and fall back to “full-open” range.

I haven’t studied your demo because it displays errors when opened (#VALUE!) but it may be caused by the outdated LO version on the computer I use today.

My new path is to separate the “Total” summary from bulk data “Amounts” into different sheet tabs of the same document. Maybe it is a bad idea ergonomy-wise, I’ll see that later.

#11

If the bulk data sheet contains only numbers, it works fine. But it is usually better to describe what the columns are for. Unfortunately, titles are not numbers and SUM(b:b) will display error #NAME?. I thought then to add a “disabling” column A where I’d put a flag like ‘*’ or ‘Z’’ to tell the row does not contain numeric data and use SUMIF() to keep only numeric rows. I don’'t intend to flag “normal” rows, which leaves the A-cell empty.

#12

Apparently, criterion “<>”“Z”"" in SUMIF() does not work on empty cell while standard formula =X9<>“Z” returns TRUE on an empty cell. Have also used regular expression “^$” but probably applies to Writer and not to SUMIF in Calc.

#13

Sorry. I don’t unsertand that any more. What do you think to need the additional quotation marks for?
If this is meant to mean “Sum the range B:B for me only taking in account the rows where the cells in A:A are neither blank nor containing the empty text.” you should use =SUMPRODUCT(B:B;A:A<>"") for clarity.
Ranges containing entire columns/rows in A:A/1:1 notation are accepted only by V5.0 or higher. Efficieny will suffer from huge ranges.

#14

Testing cells for not containing the one-letter-text Z using the ‘Criterion’ concept is done by "<>Z" in the respective parameter place. (Ctriteria are always evaluated into one string per cell to test.)

#15

Vielen Dank für Ihre Geduld!

The SUMPRODUCT is the pragmatic solution and it works for the initial question. (I feel rather comfortable with Writer, but I don’t practice Calc enough and my question and comments show my lack of knowledge of built-in functions).

Please make your comment an answer so that I can and upvote it; it will be better than upvote the comment.

#16

Remark about the criterion in SUMIF and others: writing "<>Z" looks ambiguous to me unless testing for value of name Z is forbidden. This is why I wrote "<>""Z""" to get the ‘formula’ <>"Z" after interpretation to emphasize I want to test string Z and not the value of name Z.

From trials and errors, I see my intuition is wrong, but it gives a counter-intuitive criterion for empty cell as <> or =, reduced to the comparison operator.

#17

Why do you expect Z to be interpreted as a name referring to a value? Did you define it as a named expression? Is Z just a placeholder for a cell reference?
If Z can return a value at all, the correct way to give your criterion is "<>"&Z.
If Z is a literal "<>Z" is the way. You may ‘blow it up’ to “<>”&“Z”.
A pure “<>” will test for ‘NotZero’ as numeric comparison is the origin of the criterion concept.
Anyway the criterion parameter is evaluated and passed as a string.

#18

I’m too much used to traditional programming languages where you “advertise” the compiler about string value by delimiting it with quotes. Now, thanks to your examples, I understand that a “complex” expression must be built with & and non-string operands where evaluation is required so that conversion happens before concatenation. Maybe, this should be emphasizes in the document or built-in help.

Thanks again for correcting me.

#19

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.