Created attachment 198445 [details] formula using booleans from ranges Hi! In the attached "boolean.ods" I've filled the columns A (named range data1) and B (named range data2) with random numbers (row 7 to 21). The columns C (named range bool1) and D (named range bool2) contain booleans calculated using the data ranges data1 and data2. Eventually the formula in column E should calculate a boolean operation using data of the ranges bool1 and bool2. But this fails. The results aren't correct. When I separately check the booleans, like in colomns F ( =IF(bool1;IF(bool2;TRUE();FALSE());FALSE()) ) and G ( =IF(bool1;IF(bool2;data1+data2;"bool2=True");"bool1=True") ), it works. Is there something I might have done wrong, or is this a bug? Kind regards, Willy
Reproducible Version: 24.8.4.2 (X86_64) / LibreOffice Community Build ID: bb3cfa12c7b1bf994ecc5649a80400d06cd71002 CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Vulkan; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded
But I don't think it is a bug. The function evaluate as ranges, not as arrays. You can test it putting the whole values in bool1 and bool2 as 1 e.g. It is possible to achieve what you want with =AND(bool1*bool2)
(In reply to m_a_riosv from comment #2) > But I don't think it is a bug. > The function evaluate as ranges, not as arrays. > You can test it putting the whole values in bool1 and bool2 as 1 e.g. > > It is possible to achieve what you want with > =AND(bool1*bool2) According to the documentation the function AND should support "cell ranges of logical values". https://help.libreoffice.org/latest/en-US/text/scalc/01/04060105.html?DbPAR=CALC When using the formula "= bool1 & bool2" I get correct values like "01", "00", "10" or "11". But the logical functions AND, OR, XOR fail to handle named ranges. All these function accept an array of expressions as arguments. But each argument (expression) should be treated like in other formulas. > The function evaluate as ranges, not as arrays. Which function do you mean? What means evaluate as ranges? For me a "named range" is an array or a matrix. In other words a two dimensional area within the sheet.
By the way, the function NOT to my opinion correctly resolves the pure "named range". So the formula "=AND(NOT(NOT(bool1)); NOT(NOT(bool2)))" works fine. I also tested some other functions accepting several arguments like CONCAT, CONCATENATE, SUM. While CONCATENATE resolves the named range as I expected, the functions CONCAT and SUM always take the content of the whole range. In general it should be clear, if the function resolves the values out of the range row by row or it takes the content of the whole range. May be for the logical functions AND, OR and XOR there will be a second function like it is for CONCATENATE and CONCAT, where it is clear how they handle ranges.
The function CONCAT isn't listed on the page of the "Text Functions". https://help.libreoffice.org/latest/en-US/text/scalc/01/04060110.html?DbPAR=CALC
(In reply to Willy from comment #3) >... > According to the documentation the function AND should support "cell ranges > of logical values". > https://help.libreoffice.org/latest/en-US/text/scalc/01/04060105. > html?DbPAR=CALC > > When using the formula "= bool1 & bool2" I get correct values like "01", > "00", "10" or "11". > But the logical functions AND, OR, XOR fail to handle named ranges. > All these function accept an array of expressions as arguments. But each > argument (expression) should be treated like in other formulas. > > > The function evaluate as ranges, not as arrays. > Which function do you mean? AND() AND(a1:a2;b1:b2) = AND(a1;a2;b1;b2) > What means evaluate as ranges? > For me a "named range" is an array or a matrix. > In other words a two dimensional area within the sheet. For you maybe, but it depends on the context and the function to be able to handle it as an array. Please, @Eike could you let a comment.