Bug 76891 - FILEOPEN formula issues when importing Excel 2003 spreadsheet
Summary: FILEOPEN formula issues when importing Excel 2003 spreadsheet
Status: RESOLVED DUPLICATE of bug 65746
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: LibreOffice (show other bugs)
Version:
(earliest affected)
4.1.3.2 release
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-04-01 09:02 UTC by Moose
Modified: 2016-08-18 08:26 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Screen shots and Excel spreadsheet (1.21 MB, application/zip)
2014-04-01 09:02 UTC, Moose
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Moose 2014-04-01 09:02:44 UTC
Created attachment 96701 [details]
Screen shots and Excel spreadsheet

I have imported an Excel spreadsheet into LibreOffice.  The spreadsheet contains a few worksheets and and formulas summarising and totalling the data from the various sheets into the main sheet.  The main sheet also contains a bunch of charts.

The software I am using are:
* Excel 2003 (v11.8404.8405) SP3:
* LibreOffice v4.1.3.2


*** Graph / Chart Issues:

* LibreOffice formatting issues - lots of thick vertical lines added to graph (already changed these to thin 0.01cm dashed lines in the screen shot), tick mark settings ignored, trend function formula moved, etc.

* LibreOffice has deleted my secondary trend line (level 5 quadratic trend line has been deleted).


*** Formulas NOT working / NOT returning correct values:

* LibreOffice: from Nov-2007 onwards, ALL formulas are returning incorrect values.

Here are the formulas from Excel and LibreOffice for one cell: 2008-Jan:

Excel 2003 (v11.8404.8405) SP3:
=IF((SUMIF('Occupancy by Month'!$A:$A,CONCATENATE(">=1-",C$2,"-",$B8),'Occupancy by Month'!$R:$R) - SUMIF('Occupancy by Month'!$A:$A,CONCATENATE(">=1-",D$2,"-",$B8),'Occupancy by Month'!$R:$R)) / C$1 = 0, "-", (SUMIF('Occupancy by Month'!$A:$A,CONCATENATE(">=1-",C$2,"-",$B8),'Occupancy by Month'!$R:$R) - SUMIF('Occupancy by Month'!$A:$A,CONCATENATE(">=1-",D$2,"-",$B8),'Occupancy by Month'!$R:$R)) / C$1)

LibreOffice v4.1.3.2
=IF((SUMIF($'Occupancy by Month'.$A$1:$A$1048576,CONCATENATE(">=1-",C$2,"-",$B8),$'Occupancy by Month'.$R$1:$R$1048576)-SUMIF($'Occupancy by Month'.$A$1:$A$1048576,CONCATENATE(">=1-",D$2,"-",$B8),$'Occupancy by Month'.$R$1:$R$1048576))/C$1=0,"-",(SUMIF($'Occupancy by Month'.$A$1:$A$1048576,CONCATENATE(">=1-",C$2,"-",$B8),$'Occupancy by Month'.$R$1:$R$1048576)-SUMIF($'Occupancy by Month'.$A$1:$A$1048576,CONCATENATE(">=1-",D$2,"-",$B8),$'Occupancy by Month'.$R$1:$R$1048576))/C$1)


Attached is a ZIP file containing screen shots and the Excel spreadsheet.  I have drawn red boxes around the values and chart areas that are wrong / have issues in LibreOffice screen shot.

Hope you can fix this.

Moose
Comment 1 tommy27 2014-07-27 20:17:37 UTC
issue confirmed under Win7x64 using LibO 4.2.5.2 and 4.4.0.0.alpha0+
Build ID: b9dca968c6fd0ab5ca140c65b0e54d153cd34986
TinderBox: Win-x86@42, Branch:master, Time: 2014-07-18_22:51:20

I add Calc developer to CC list
Comment 2 QA Administrators 2015-09-04 02:48:58 UTC Comment hidden (obsolete)
Comment 3 Buovjaga 2015-11-19 16:49:42 UTC
Still confirmed.

Moose: you should create a separate bug report for every single issue. Then you can add a comment here linking to them and we will confirm them.

Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+
Build ID: 66d2b72667792cb18b25805387824d636e2a455c
TinderBox: Win-x86@39, Branch:master, Time: 2015-11-18_02:35:53
Locale: fi-FI (fi_FI)
Comment 4 Alex Thurgood 2016-08-18 08:23:17 UTC
The incorrect import of formula/formulae is almost certainly a DUP of bug 65476. I am marking this as DUP of that bug. 

I suggest that the original reporter open separate reports for the other problems.
Comment 5 Alex Thurgood 2016-08-18 08:25:26 UTC
Sorry, I meant bug 65746
Comment 6 Alex Thurgood 2016-08-18 08:26:26 UTC

*** This bug has been marked as a duplicate of bug 65746 ***