Bug 92915 - Formular Error in Calc
Summary: Formular Error in Calc
Status: CLOSED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-07-24 13:20 UTC by Karl-W. Hoffmann
Modified: 2016-08-04 13:45 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Error in CALC-Formula (116.13 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-08-05 14:06 UTC, Karl-W. Hoffmann
Details
Error in Calc Formula (100.11 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-08-05 14:11 UTC, Karl-W. Hoffmann
Details
SUM Func not working example.ods (12.45 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-08-07 11:50 UTC, Alexander Nolting
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Karl-W. Hoffmann 2015-07-24 13:20:09 UTC
A Spreasheet of several Pages, constructed under Excel 2010, works fine with Ecel 2010 and 2013.

Using Calc, I get on several Fields in different Pages the Error:  #NAME?

I tracked down to a Cell on Page 2 and found the formular: 

   =SUMMEWENNS(Betrag;[0]!konto;2110;VA;"M")

When I remove this Formular, the #NAME? - Error is gone. Some calculations on the Sites with the #NAME? - Error are not calculating any more.

The Spreadsheet is from a Choral societey and on one of the not functional pages calculate summery for the finance Office.

Any Idea for a Solution?

Kindly yours
Karl
Comment 1 m_a_riosv 2015-07-24 14:17:42 UTC
Hi @Karl-W, thanks for reporting.

Table references are not supported in your version. It will be for 5.0: https://wiki.documentfoundation.org/ReleaseNotes/5.0#Table_structured_references

Please also see https://bugs.documentfoundation.org/show_bug.cgi?id=91278#c5.
Comment 2 Karl-W. Hoffmann 2015-08-05 13:17:51 UTC
Hi,
I installed LibreOffice 5.0.0.5 and I sill have the same problem as already reported.

   =SUMMEWENNS(Betrag;[0]!konto;2110;VA;"M")

still creates the Error "#Name?"

I tried to Change the Name "konto" to Konto and got Err:507.
When I delete "[0]!" i got Err:520.

Any Help is apprecated.

Karl
Comment 3 m_a_riosv 2015-08-05 13:44:34 UTC
Please could you attach a sample file, without private data, as minimal as possible.
Comment 4 Karl-W. Hoffmann 2015-08-05 14:06:05 UTC
Created attachment 117671 [details]
Error in CALC-Formula

Hi,

attached please find the File without private Data.

I tracked down the Error to Page 2 (Auswertungen) - Cell D28.
When I remove the Formula in this Cell, the Error an Page 1 (Kasse) Cell H5 disappears.
However, the calculation on Page4 "Finanzamts-Auswertung" and some other pages does not work.
In Excel the Formula and the whole Spread - Sheet works fine.

Kind Regards
Karl
Comment 5 Karl-W. Hoffmann 2015-08-05 14:11:18 UTC
Created attachment 117673 [details]
Error in Calc Formula
Comment 6 Karl-W. Hoffmann 2015-08-05 14:18:56 UTC
I tried to send the original xxx.XLSX File, but it does not work.
I converted the File to xxx.ODS Format and now the Cell H5 is empty, the Eroor on the other Cells are still exists.

Totally confused
Karl
Comment 7 m_a_riosv 2015-08-05 17:05:19 UTC
I think it has nothing to do with Table reference the [0] guides me to the error.

For the formula:
=SUMMEWENNS(Betrag;[0]!konto;2110;VA;"M")

The [0]! in the formula I think is the issue.
How it was introduced?, It's in the xlsx uploaded file, Is this xlsx file the original that works in Excel?

In the cell below Auswertungen.D29 there is a similar formula without it.
Comment 8 Karl-W. Hoffmann 2015-08-05 17:38:09 UTC
This is the original Excel-Sheet. I just delete the Membersname (Mitglieder) and the names on some other pages.

The Formular (   =SUMMEWENNS(Betrag;[0]!konto;2110;VA;"M") ) is originally in Cell D28 and YES, the Spread-Sheet works perfektly in Excel.

I got this spread sheet from another person that did my Job in the past.
He advised me to buy the Office Package to get the thing working.
Comment 9 m_a_riosv 2015-08-05 21:53:20 UTC
Then change to unconfirmed.

Perhaps someone with excel can test.
Comment 10 m_a_riosv 2015-08-05 21:56:15 UTC
If it is a named range with sheet scope, as I know it is not supported by LibreOffice between different sheets.
Comment 11 Alexander Nolting 2015-08-07 11:47:23 UTC
Hello,

I have an similiar issue with SUMME() (SUM in english) function. It does not summarize properly cells. It doesn't what the cell format is set to.

So I decided not to create an own Bug Report as it might be the issue here.
Comment 12 Alexander Nolting 2015-08-07 11:48:05 UTC
Forgot to say LO 5.0.0.5 on Linux
Comment 13 Alexander Nolting 2015-08-07 11:50:42 UTC
Created attachment 117741 [details]
SUM Func not working example.ods
Comment 14 Karl-W. Hoffmann 2015-08-07 15:45:44 UTC
I don't understand the last two Messages.

As far as I understand, the last Part of the Formular shall write a "M" (for the "man-only" Chorus) in Columne "G" on the first side (Kasse).
All other calculations of the formula are out of my understnding.

I do not have a buddy, that understand Excel AND Calc to solve this issue.

Karl
Comment 15 Alexander Nolting 2015-08-07 16:52:05 UTC
Hello Karl,

I digged into your issue and found that LO doesn't support the {COUNT,SUM,AVERAGE}IFS starting from Excel 2007 onwards. Below you find an idea to substitute this by using SUMPRODUCT().


SUMIFS( range ; column1 ; ">1" ; column2 ; "<5" ) <==> SUMPRODUCT( range * column1>1 * column2<5 )
COUNTIFS( column1 ; ">1" ; column2 ; "<5" ) <==> SUMPRODUCT( column1>1 * column2<5 )
AVERAGEIFS <==> SUMIFS/COUNTIFS

My last two comment are for Miguel.

Best regards
Alex
Comment 16 m_a_riosv 2015-08-07 17:30:38 UTC
Hi Alexander,

1. May be comments #13 and #14 are for other bug report. I can see the relation of attached file with this.

2. The SUMIFS COUNTIFS AVERAGEIFS are in LibreOffice at least from 4.2
Comment 17 m_a_riosv 2015-08-07 17:31:26 UTC
1. May be comments #13 and #14 are for other bug report. I **can't** see the relation of attached file with this.
Comment 18 Alexander Nolting 2015-08-07 18:04:26 UTC
Hello Miguel,

After a quick search for bugs related to lo5 I thought there might be a relation between this bug and my issue. 

So I will file a new report.

Are you able to remove my attachment from this bug report?

Regards
Alex
Comment 19 Alexander Nolting 2015-08-07 18:08:31 UTC
off topic: can you point to the documentation where SUMIFS etc. is described?

As I can see SUMIF is support not SUMIFS.
Comment 20 m_a_riosv 2015-08-07 21:05:39 UTC
Hi Alexander, comment#18, sent a PM to you.

I think it's pending write the help for those functions.
https://bugs.documentfoundation.org/show_bug.cgi?id=64773#c9

But you can find it as any other function in the function wizard.
The icon on the input bar to the left of the sum's icon.
Comment 21 Karl-W. Hoffmann 2015-08-08 14:05:49 UTC
(In reply to Alexander Nolting from comment #11)
> Hello,
> 
> I have an similiar issue with SUMME() (SUM in english) function. It does not
> summarize properly cells. It doesn't what the cell format is set to.
> 
> So I decided not to create an own Bug Report as it might be the issue here.

Alex,

I treid the following.

On my Desktop (WIN7 Prof; LO 5.0.0.5) I opened a new Spreadsheet, set some cells to TIME (12:34h) and added some hours and Minutes to the Cells. Finally I pushed the SUM Button and the Summerization of the Cells worked fine.
On my Laptop (WIN7 Prof, LO 4.4.4.3) I could veryfy this.
There must be some Issue in the General Set Up of you Spread-Sheet I Gues.

Karl
Comment 22 Alexander Nolting 2015-08-08 14:12:25 UTC
Hello Karl,

after updating this morning to LO 5.0.0.5-324.3 from tumbleweed the sum func is working again. There are only a small glitch where the app doesn't recognize that a formula is added to the cell. This can be worked around by using the formula assistant that many time until the cell is been calculated by the formula.

Thanks ;)

ALex
Comment 23 Karl-W. Hoffmann 2015-08-08 14:34:11 UTC
(In reply to Alexander Nolting from comment #15)
> Hello Karl,
> 
> I digged into your issue and found that LO doesn't support the
> {COUNT,SUM,AVERAGE}IFS starting from Excel 2007 onwards. Below you find an
> idea to substitute this by using SUMPRODUCT().
> 
> 
> SUMIFS( range ; column1 ; ">1" ; column2 ; "<5" ) <==> SUMPRODUCT( range *
> column1>1 * column2<5 )
> COUNTIFS( column1 ; ">1" ; column2 ; "<5" ) <==> SUMPRODUCT( column1>1 *
> column2<5 )
> AVERAGEIFS <==> SUMIFS/COUNTIFS
> 
> My last two comment are for Miguel.
> 
> Best regards
> Alex

Hello LO Calc Supporters:
I had the Oportunity to use Excel 2013 and saved the Spread-Sheet from Excel as xxx.ODS File.
Opening this File (xxx.ODS) in LO (Desktop: WIN7 Prof-64Bit LO 5.0.0.5 and Laptop: WIN7 Prof-64Bit LO4.4.4.3) does NOT vreate any Error-Message.
It seems to me, that the Spread-Shet is working perfektly under LO mow.

The "Suspected Cell "Auswertung D28" does now as Formula show this Contend:

   "=SUMMEWENNS(Betrag;'file:///C:/Users/K-W.Hoffmann/Desktop/Kasse%20SV%202015.ods'#$$Konto;2110;VA;"M")"

Is it possible, that the conversation of XLSX Format to the LO Format during th File-Opening?
 Tao all of you a nice Week-End.
Karl
Comment 24 Alexander Nolting 2015-08-08 14:43:55 UTC
Hello Miguel,

I got another update from tumbleweed again and the glitch where the cell is not calculated by a formula manually entered is also disappeared.
 
So I'm fine now.

Thanks to you both, Karl and Miguel.

Alex
Comment 25 raal 2015-08-21 11:01:51 UTC
When opening file Kasse SV 2015_Test-Copy.xlsx in excel 2010 I get error "excel found unreadable content, recovery ... etc." After recovery is file opened.

File  Kasse SV 2015_Test-Copy.xlsx, Sheet Auswertungen, Cell D28
LO:
=SUMIFS(Betrag;[0]!konto;2110;VA;"M")

excel 2010:
=SUMIFS(Betrag;'Kasse SV 2015_Test-Copy.xlsx'!Konto;2110;VA;"M")

=> the problem is in reference 'Kasse SV 2015_Test-Copy.xlsx'!Konto -  it's reference to the same file. For example cell D29 contain formula =SUMIFS(Betrag;Konto;2110;VA;"G") and this formula is recognized in LO correctly (=SUMIFS(Betrag;Konto;2110;VA;"G")). Konto is named range =Kasse!$D:$D.

To "fix" this file you can delete 'Kasse SV 2015_Test-Copy.xlsx'! -  so D28 =SUMIFS(Betrag;Konto;2110;VA;"M")


Setting as new / formula works in excel, doesn't work in LO.
Comment 26 Eike Rathke 2016-08-01 13:59:54 UTC
The problem is that already the first document attached to this bug is saved by Calc with the broken formula expression part. To fix anything we'd need the original, presumably saved by Excel, document file.
Comment 27 Eike Rathke 2016-08-04 13:45:07 UTC
I received the original confidential .xlsx file to reproduce.
The good news is, I could not reproduce anything bad when using 5.0.6, 5.1.5 or 5.2.0 to open and save/reload the file.
The bad news is, there's nothing to fix then..

In the original document I also don't see anything that could had produced the erroneous [0]!konto named reference.

I assume this was a one time glitch with unknown cause, maybe happening only in one specific version. I can only close as worksforme.