[Libreoffice-bugs] [Bug 126519] New: UI - Calculation incorrect with VLOOKUP function, will not correct on recalculate

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=126519

            Bug ID: 126519
           Summary: UI - Calculation incorrect with VLOOKUP function, will
                    not correct on recalculate
           Product: LibreOffice
           Version: 6.1.5.2 release
          Hardware: All
                OS: Linux (All)
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: alex at patternjugglers.co.nz

Description:
Some cells with an VLOOKUP formulas fail to calculate or recalculate correctly,
despite formula being identical to cells above and below.
Issue survives recalculation and save/reopen of file. 
Can ultimately be resolved by copying or re-entering exactly the same formula
or changing value in a precedent cell, but it seems unacceptable that it
otherwise remains incorrect.

Steps to Reproduce:
Note on reproducibility - it is 100% reproducable with specific cells in a
particular sample file, but I haven't attempted to arbitrarily create the issue
in a new file
1.  Cells have existing, correct VLOOKUP formula, and display incorrect result
2.  Other cells in column with same formula (and same value, but differently
located precedent cells) display correctly

Actual Results:
Reopen or recalculate retains incorrect value.
Whereas editing and reverting the formula, or changing precedent cell calculate
correctly, so result cannot be relied on

Expected Results:
Would expect cells with same formula to always return same result


Reproducible: Sometimes


User Profile Reset: No


OpenGL enabled: Yes

Additional Info:
-openSUSE Version: 6.1.5.2 Build ID: 10(Build:2), and 
-Windows Version: 6.2.4.2 (x64) Build ID:
2412653d852ce75f65fbfa83fb7e7b669a126d6

Re userprofile: it is occurring for 2 users on 3 installations.

Further details:

Background
    1. The spreadsheet is a household budget in ODS format, where we import or
enter transactions
    2. Presently 9500+ rows, but performance seems fine (and we periodically
paste values only on old rows to lessen formula load).
    3. We assign categories to transactions (eg insurance, grocery) in column C
    4. In column J, we have a vlookup function against a named range to provide
a group for the categories (eg Income, Expenses, Transfers)
The formula is =VLOOKUP($C9503,CatGroups,2,0)
    5. For the past 4 years this has performed exactly as expected


Description of current Problem
In recent weeks, some of the cells in with the VLOOKUP formula in colum J fail
to calculate.
In an area where it occurs:
    • all cells in column C contain the same value
    • some cells in column J has calculated correctly from the VLOOKUP,
returning "EXPENSES"
    • other cells in column J are displaying 0.00, which is incorrect

In testing I have established:
    1. Cell formula contents are identical apart from the relative address of
eg $C9503, which alters by row only
    2. Setting calculation to manual or automatic does not fix
    3. Forced manual recalculation does not fix
    4. Saving and reopening does not fix
    5. Opening the file in openSUSE or Windows instances, and by different
users produces the same error
    6. Copying an existing correctly-calculating cell from Column J DOES FIX
newly pasted cells only, as does filling down with a drag handle.
    7. Manually changing the formula in the cell to something else (eg change
index from 2 to 1), and then reverting DOES FIX.
    8. Performing an edit on the precedent cell eg $C9503 DOES FIX.
This works even if the value is unchanged, eg F2 to edit, add a trailing space
then delete it, and press ENTER to save the unchanged value "grocery"

Comments
While the impacted cells can be found and fixed, it is not easy an easy problem
to diagnose, and users should be able to trust formulas to calculate correctly!

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20190724/549ae4fc/attachment.html>