Created attachment 194307 [details] XLSX sample file The function AVERAGE should not consider logical values as numeric. See: https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6 However, logical values are taken into consideration in Calc, causing compatibility issues in sheets with such values. Steps to reproduce: 1) Open the attached XLSX file in Calc (the file was originally created in Excel, so as soon as it opens, all the results from the formulas will be correct). 2) Notice that all results in Column G are 14.25 (which is correct) 3) Now recalculate the sheet (Data - Calculate - Recalculate Hard / or Ctrl+Shift+F9) 4) Notice that the results in Column G now changed to 11.6 in rows 2 and 3 So it appears that logical values are counted... text is correctly not considered. System info Version: 24.2.3.2 (X86_64) / LibreOffice Community Build ID: 420(Build:2) CPU threads: 16; OS: Linux 6.8; UI render: default; VCL: kf5 (cairo+xcb) Locale: pt-BR (pt_BR.UTF-8); UI: en-US Ubuntu package version: 4:24.2.3~rc2-0ubuntu0.24.04.1~lo1 Calc: threaded
If I may, I'd like to emphasize the following point, JIC. (In reply to Rafael Lima from comment #0) > However, logical values are taken into consideration in Calc, causing > compatibility issues in sheets with such values. In ODF, this is a known factor, as boolean values in ODF are numeric values; there is no "logical" type. The issue is how Calc should import/export logical values (and TRUE/FALSE strings, on each respective language/locale) when dealing with external file formats such as XLS and XLSX.
ODF _differentiates_ between implementations with and without distinct logical boolean types. See https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1017898_715980110 and https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#Distinct_Logical There is no distinct logical boolean type in Calc. This is also not specific to AVERAGE(), all functions taking a NumberSequence are affected, like SUM() et al. Btw, try this in Excel: =AVERAGE(TRUE(),FALSE()) vs A1: =TRUE() A2: =FALSE() A3: =AVERAGE(A1:A2) Importing logical boolean values as anything else than numeric values is not an option (unless we throw away all current handling and come up with a distinct logical boolean type), and treating any strings as something different than strings would be plain wrong. Yes, Calc and Excel differ in this regard.