# calc - compute range based on boolean values

Let's say I have a range

 OFFSET($AA:$AA,start_,0,stop_-start_,1)~OFFSET($BD:$BD,start_,0,stop_-start_,1)~OFFSET($CG:$CG,start_,0,stop_-start_,1)


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 close merge delete

## Comments

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.

( 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.

( 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.

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

Yes it is.

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

Submitted bug as tdf#104906.

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