Bug 145732 - Formula that works in Excel, but not calc
Summary: Formula that works in Excel, but not calc
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.2.2.2 release
Hardware: All Windows (All)
: medium minor
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-11-17 09:46 UTC by telrod11
Modified: 2021-11-17 11:43 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Attachments
Sample of the SUM issue (5.11 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2021-11-17 10:36 UTC, telrod11 		Details
Excel also does not evaluate that formula *in that cell* (49.20 KB, image/png)
2021-11-17 10:51 UTC, Mike Kaganski 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description telrod11 2021-11-17 09:46:14 UTC 
Description:
This is a simple formula, but will fail without manipulation in Calc...

HEre it is in Excel:
=IF(B28=0,"",(B28:N28))

In Calc, it requires teh additional "SUM"
=IF(B28=0,"",SUM(B28:N28))

No big deal, but seems to be a formula that should work without the "SUM"


Steps to Reproduce:
1.Type formula as it is in Excel
2.Fails
3.

Actual Results:
USe the formula stated above, as you would in Excel, and it requires the additional SUM

Expected Results:
Not #VALUE


Reproducible: Always


User Profile Reset: No



Additional Info:
[Information automatically included from LibreOffice]
Locale: en-US
Module: StartModule
[Information guessed from browser]
OS: Windows (All)
OS is 64bit: no
Comment 1 Eike Rathke 2021-11-17 10:11:37 UTC 
Both formulas work, but do different things. And placing a SUM() there quite certainly is not the correct solution if just the B28:N28 was supposed to work. The #VALUE error you see for the first formula most likely is because there is no intersection of the formula cell position with the cell range B28:N28.

So please elaborate what exactly you are trying to accomplish, best attach a sample document.
Comment 2 telrod11 2021-11-17 10:36:51 UTC 
Created attachment 176308 [details]
Sample of the SUM issue
Comment 3 Mike Kaganski 2021-11-17 10:51:29 UTC 
Created attachment 176309 [details]
Excel also does not evaluate that formula *in that cell*

(In reply to telrod11 from comment #2)

The attached file does *not* work in Excel - exactly for the reason that erAck stated: there's no unambiguous intersection.

I'd say it's not a bug.
Comment 4 Eike Rathke 2021-11-17 11:43:05 UTC 
Right, not a bug.
In the attached document the formula would work in any cell of column A to M because only then an implicit intersection can be formed, but not in the same row; also, the formula is not expected to perform a sum on the range, but pick one value of the range at the position of the intersection.