Bug 67843 - VIEWING: Advanced Excel Sheet doesn't operate as intended
Summary: VIEWING: Advanced Excel Sheet doesn't operate as intended
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: Other Windows (All)
: medium normal
Assignee: Not Assigned
Whiteboard: BSA
Depends on:
Reported: 2013-08-06 20:17 UTC by Maverynthia
Modified: 2013-11-20 15:51 UTC (History)
0 users

See Also:
Crash report or crash signature:
Regression By:

Stalk Market Guide BETA 3 (87.97 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-08-06 20:17 UTC, Maverynthia

Note You need to log in before you can comment on or make changes to this bug.
Description Maverynthia 2013-08-06 20:17:44 UTC
Created attachment 83735 [details]
Stalk Market Guide BETA 3

Problem description: 
I'm trying to use an advanced Excel sheet with fancy graphs and such to track the price and the trend of prices. There is two version of the file and they both act similarly.
v 2.01: http://thoughtification.tumblr.com/post/57231055897/stalk-market-master-beta-2-whether-youre-new
v 3.00: http://ac-pokan.tumblr.com/post/57440445398/stalk-market-master-beta-3

Steps to reproduce:
1. Open
2. Enter info

Current behavior:
Can't enter in a sell price, graph supposed not color coded, ERR 509.

Expected behavior:
Able to enter a sell price and place, graphs supposed to be color coded, trend advice is supposed to be NONE or display a trend, such as "big spike"

Operating System: Windows XP
Version: release
Comment 1 Mike Kaganski 2013-08-07 01:25:16 UTC
There are problems with the attached file.

Cell A36 in worksheet "Blank (8)" contains the following formula:

=IF(AND(OR(AND(C4<C5; C5<C6; C6<C7; C7<C8 C8>0);AND(C5<C6; C6<C7; C7<C8; C8<C9; C9>0);AND(C6<C7; C7<C8; C8<C9; C9<C10; C10>0);AND(C7<C8; C8<C9; C9<C10; C10<C11; C11>0);AND(C8<C9; C9<C10; C10<C11; C11<C12; C12>0);AND(C9<C10; C10<C11; C11<C12; C12<C13; C13>0);AND(C10<C11; C11<C12; C12<C13 C13<C14; C14>0);AND(C11<C12; C12<C13; C13<C14; C14<C15; C15>0)));1;0)

You may notice that there's no semicolon between "C7<C8" and "C8>0" in first innermost "AND". The same problem is in next-to-last innermost "AND": no semicolon between "C12<C13" and "C13<C14".

The formula is malformed. LO correctly displays error 509 (and description in status bar: "Error: Operator missing"). If the formula is corrected, this worksheet seems to work (cells C4-C15 are colored on input; the graph is drawn; advice in H17 is given).

The problem in formula is in the file itself; it is not introduced by Calc. It may be checked, if you open the xlsx ar zip, and inside it look into xl\worksheets\sheet8.xml. The corresponding formula there look like this:

<f>IF(AND(OR(AND(C4&lt;C5, C5&lt;C6, C6&lt;C7, C7&lt;C8 C8&gt;0),AND(C5&lt;C6, C6&lt;C7, C7&lt;C8, C8&lt;C9, C9&gt;0),AND(C6&lt;C7, C7&lt;C8, C8&lt;C9, C9&lt;C10, C10&gt;0),AND(C7&lt;C8, C8&lt;C9, C9&lt;C10, C10&lt;C11, C11&gt;0),AND(C8&lt;C9, C9&lt;C10, C10&lt;C11, C11&lt;C12, C12&gt;0),AND(C9&lt;C10, C10&lt;C11, C11&lt;C12, C12&lt;C13, C13&gt;0),AND(C10&lt;C11, C11&lt;C12, C12&lt;C13 C13&lt;C14, C14&gt;0),AND(C11&lt;C12, C12&lt;C13, C13&lt;C14, C14&lt;C15, C15&gt;0))),1,0)</f>

If MS products are more forgiving to the errors in the formulas, it does not mean that other programs must excercise the same tolerance. LO checks formula syntax more strictly, and IMO this is good. The source file must be corrected. You may file a bug report to the author(s) of that file.

Closing as NOTOURBUG.