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@lists.freedesktop.org
Reporter: a...@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.
_______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs