calc - compute range based on boolean values

asked 2016-11-14 11:12:36 +0200

DiesNuts gravatar image

Let's say I have a range


And let's say I have three boolean fields named valid1, valid2, valid3.

How do I change the range formula s.t. OFFSET($AA:$AA,start_,0,stop_-start_,1) only gets in cluded when valid1 is true, OFFSET($BD:$BD,start_,0,stop_-start_,1) only when valid2 is true and OFFSET($CG:$CG,start_,0,stop_-start_,1) only when valid3 is true?

I.e. when, for example, (valid1,valid2,valid3)==(1,0,1), the range should be reduced to OFFSET($AA:$AA,start_,0,stop_-start_,1)~OFFSET($CG:$CG,start_,0,stop_-start_,1)

edit retag flag offensive close merge delete


Since there isn't a thing like an 'Empty Range' I'm afraid there will not be a dircet way, and OFFSET might not be the appropriate means to get a workaround.
Constructs like =INDIRECT(IF(valid1;"A1:B15~";"")&"D3:Z77") are allowed. You may thus replace a reference concatenation by a text concatenation where an empty (not applicable) range is omitted by simply passing the empty text to the concatenation. I would try to avoid this.

Lupp gravatar imageLupp ( 2016-11-14 16:37:57 +0200 )edit

INDIRECT evaluates only ranges, not expressions. In case valid1 is not 0 then the concatenation would produce the text A1:B15~D3:Z77 which INDIRECT does not digest due to the range union/list operator being used.

erAck gravatar imageerAck ( 2016-11-19 02:58:45 +0200 )edit

Sorry! I had tested instead of first studying the specifications, and my testing was not thorough enough. Basically @erAck is right. There is, however, an additional marginal remark:
The colon is specified for two different purposes by OpenFormula: It is used as a syntactical element of range addresses (5.8), and may also be an operator usable with references (6.4.11). That INDIRECT does not return an error if offered an expression but just ignores operators and operands is a bug, imo.

Lupp gravatar imageLupp ( 2016-11-19 12:20:01 +0200 )edit

Yes it is.

erAck gravatar imageerAck ( 2016-11-19 22:06:21 +0200 )edit

Submitted bug as tdf#104906.

erAck gravatar imageerAck ( 2016-11-21 22:25:08 +0200 )edit