Download it now!
Bug 106575 - Correct VLOOKUP Formula - Incorrect Value shown. Shows correct value if formula reapplied / changes made in other sheets
Summary: Correct VLOOKUP Formula - Incorrect Value shown. Shows correct value if formu...
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.3.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Function-Vlookup
  Show dependency treegraph
 
Reported: 2017-03-16 14:19 UTC by Amit Seal Ami
Modified: 2017-12-15 08:32 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Bug affected sample file (35.54 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-03-19 14:51 UTC, Amit Seal Ami
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Amit Seal Ami 2017-03-16 14:19:23 UTC
Description:
I used vlookup to copy values from other sheets. Although the same formula is applied throughout the column, some values are not correct. 
If I reapply the same formula, it shows the correct value. If I make some changes in the sheet being used by vlookup, it changes the incorrect value to correct ones.

The file exhibiting this behavior contains sensitive information. If those information are removed(change data), the formula calculation is triggered by changes and incorrect values are replaced by correct values. However, if I do not save and close the file and reopen it, the incorrect values are shown every.single.time. 

I have screencasted this behavior and it is available here for viewing for getting a clearer idea.

https://www.youtube.com/watch?v=4oakCHd6FOQ&feature=youtu.be

I am really sorry that I can not share this file due to the information it contains. 

I am stating it as severe, since such errors in data calculation completely throwsthe usefulness of libreoffice out of the window. 

Please do not think that I am not happy with LibreOffice. I really am, and I will provide any information necessary so that you can fix it.

Steps to Reproduce:
Only a few values are incorrect, and I am not sure what triggers it. 

Actual Results:  
Incorrect result from formula.

Expected Results:
Correct value taken from other sheets using vlookup.


Reproducible: Couldn't Reproduce

User Profile Reset: No

Additional Info:
Kubuntu: 16.10, 64 bit.



User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.98 Safari/537.36
Comment 1 Xisco Faulí 2017-03-16 17:08:26 UTC Comment hidden (obsolete)
Comment 2 Amit Seal Ami 2017-03-16 17:28:14 UTC
Hi Xisco,
Thank you for your response. 
I am downloading master~2017-03-07_02.52.26_LibreOfficeDev_5.4.0.0.alpha0_Linux_x86-64_deb.tar.gz right now, and will inform as soon as possible.

Just opening the file containing the incorrect value and checking if the formula is applied properly should suffice, right?
Comment 3 Xisco Faulí 2017-03-16 17:37:56 UTC
Hi Amit,
Could you also attach the document to reproduce the problem?
Comment 4 Amit Seal Ami 2017-03-16 17:41:39 UTC
Hi Xisco,

Really can't. It contains sensitive information. Any attempt to remove any information by deleting/modifying somehow refreshes the affected sheets and shows the corrected values.
Comment 5 Amit Seal Ami 2017-03-16 18:32:11 UTC
Hi Xisco, 
I installed master~2017-03-07_02.52.26_LibreOfficeDev_5.4.0.0.alpha0_Linux_x86-64_deb.tar.gz , opened the spreadsheet file(.xlsx extension, in case that is necessary information) using LibreOffice Version: 5.4.0.0.alpha0+
Build ID: a5a6694128728c48c1a8482450a21ad49025c40c
CPU threads: 4; OS: Linux 4.8; UI render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-03-07_02:52:26
Locale: en-US (en_US.UTF-8); Calc: group.  

The incorrect value for correct formula is still present.
Comment 6 Amit Seal Ami 2017-03-19 06:31:13 UTC
(In reply to Xisco Faulí from comment #3)
> Hi Amit,
> Could you also attach the document to reproduce the problem?

Xisco, 
is it okay to remove the sensitive information and upload it? I should mention that any modification to sheets "refreshes" the values by recalculating the formula in the sheets. From the uploaded file, it might not be possible to view the values being refreshed, but it might give some idea.
Comment 7 Xisco Faulí 2017-03-19 11:32:59 UTC
(In reply to Amit Seal Ami from comment #6)
> (In reply to Xisco Faulí from comment #3)
> > Hi Amit,
> > Could you also attach the document to reproduce the problem?
> 
> Xisco, 
> is it okay to remove the sensitive information and upload it? I should
> mention that any modification to sheets "refreshes" the values by
> recalculating the formula in the sheets. From the uploaded file, it might
> not be possible to view the values being refreshed, but it might give some
> idea.

it's ok as long as the bug can be reproduced with the new data, otherwise it's not really helpful...
Comment 8 Amit Seal Ami 2017-03-19 14:51:54 UTC
Created attachment 132012 [details]
Bug affected sample file

The uploaded ods was created by: 
- unzipping the original bug affected file,
- removing the sensitive information by editing the content.xml
- zipping back the contents (following this link: http://stackoverflow.com/questions/4957212/how-does-open-office-compress-its-files)
- opening and repairing the new file
- saving it as a new ods file

I checked and the new file is also affected by the bug.
Comment 9 Amit Seal Ami 2017-03-19 14:56:44 UTC
(In reply to Xisco Faulí from comment #7)
> (In reply to Amit Seal Ami from comment #6)
> > (In reply to Xisco Faulí from comment #3)
> > > Hi Amit,
> > > Could you also attach the document to reproduce the problem?
> > 
> > Xisco, 
> > is it okay to remove the sensitive information and upload it? I should
> > mention that any modification to sheets "refreshes" the values by
> > recalculating the formula in the sheets. From the uploaded file, it might
> > not be possible to view the values being refreshed, but it might give some
> > idea.
> 
> it's ok as long as the bug can be reproduced with the new data, otherwise
> it's not really helpful...

Hi Xisco,

After trying for a few hours by trying to modify the contents programmatically  (python, pyexcel_ods, and other libraries) I finally was able to remove the sensitive data by editing the content.xml directly after unzipping.


I checked and the file is affected by the aforementioned bug. I opened it using both LibreOffice 5.3.0.3 and 5.4.0.0.alpha0+. 

I hope this will be useful.

Now, I leave the rest to you, as I gotta take some rest. (ouch, my back hurts) :D
Comment 10 Xisco Faulí 2017-03-19 15:08:58 UTC
Could you please mention the steps to reproduce it with the new file ? better to have a list of steps than a video
Comment 11 Amit Seal Ami 2017-03-19 16:54:39 UTC
(In reply to Xisco Faulí from comment #10)
> Could you please mention the steps to reproduce it with the new file?
> better to have a list of steps than a video

I am not sure how it can be reproduced. 
I've been using LibreOffice for years, and this is the first time something like this happened which only affected a few cells. I simply used vlookup to refer values from one sheet to another the usual way. Later after printing and checking, - I suddenly found that the values being shown were not correct, even though the formula was right. The formulas were applied throughout the columns by double-clicking the cell handler after writing it once.

I provided the file as a sample so that the existence of the bug can be confirmed. I am providing further details so that it is easier to understand what is being referred where.

1. The 2ndTrimester sheet contains data related to results, first column Roll is going to be used as lookup index in vlookup function. 

2. The Detailed Sheet is using IFERROR(VLOOKUP()) to get values from 2ndTrimester sheet. It is looking at the roll from the first column, using it to lookup a particular row in A22 to O60 range in 2ndTrimester Sheet, if matched - it is taking the value from the specified column, if there is any error, it is replacing the value with 0.

3. For example, check A16 in Detailed Sheet. The J16 contains the following formula: 
=IFERROR(VLOOKUP($A16,$2ndTrimester.$A$22:$O$60,5,0), 0)


for 1612, the value being of A16, the value of $2ndTrimester.$A$22:$O$60 is 3.75.
However, the value being shown at J16 is ... 0. The same goes for K16, L16, M16, J17, K17, M17, J18, K18, L18, M18, J19, K19, L19, M19. 

An interesting observation is that the bug affected columns seemingly shifted a row. Here is what I mean shown in this picture: http://imgur.com/a/Hxdq3

I hope this will be informative enough.
Comment 12 Buovjaga 2017-03-25 19:07:39 UTC
I confirm that it needs a hard recalc (Ctrl-Shift-F9). After that and saving & reloading, the values stay.

There have been some vlookup recalc bugs: bug 61662 (but it went away).

Not sure what can be done about this.

Arch Linux 64-bit, KDE Plasma 5
Version: 5.4.0.0.alpha0+
Build ID: 54f9a81a1c041c18d209d7eb4659ac18a3ca76dd
CPU threads: 8; OS: Linux 4.10; UI render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on March 25th 2016
Comment 13 Amit Seal Ami 2017-03-28 08:50:07 UTC
(In reply to Buovjaga from comment #12)
> I confirm that it needs a hard recalc (Ctrl-Shift-F9). After that and saving
> & reloading, the values stay.
> 
> There have been some vlookup recalc bugs: bug 61662 (but it went away).
> 
> Not sure what can be done about this.
> 
> Arch Linux 64-bit, KDE Plasma 5
> Version: 5.4.0.0.alpha0+
> Build ID: 54f9a81a1c041c18d209d7eb4659ac18a3ca76dd
> CPU threads: 8; OS: Linux 4.10; UI render: default; VCL: kde4; 
> Locale: fi-FI (fi_FI.UTF-8); Calc: group
> Built on March 25th 2016

Hi Buovjaga,
Thank you for the tips. I didn't know about the shortcut. I will definitely be using this shortcut every time before pressing ctrl+s.

Can not there be a feature as an option that will hard recalc the sheet when the file is closed? Doing so by default will hit performance understandably.
Comment 14 Buovjaga 2017-03-28 09:14:27 UTC
(In reply to Amit Seal Ami from comment #13)
> Can not there be a feature as an option that will hard recalc the sheet when
> the file is closed? Doing so by default will hit performance understandably.

The developers want to keep it as a development/debugging feature.
Comment 15 Amit Seal Ami 2017-03-29 09:04:09 UTC
(In reply to Buovjaga from comment #14)
> (In reply to Amit Seal Ami from comment #13)
> > Can not there be a feature as an option that will hard recalc the sheet when
> > the file is closed? Doing so by default will hit performance understandably.
> 
> The developers want to keep it as a development/debugging feature.

so... is this a confirmed bug? Anything I can do here to help?
Comment 16 Buovjaga 2017-03-29 09:40:20 UTC
The document contains cached values and hard recalc helps with that. Here is an example of a similar case: https://bugs.documentfoundation.org/show_bug.cgi?id=82135#c18 I will close this as notabug just like that other.
Comment 17 Amit Seal Ami 2017-12-15 08:32:07 UTC
Dear Buovjaga,
Sorry to revive this dead thread again; but this bug hit me again. 
Since I am in academia, this is a issue that affects the grade of students if the values are not hard recalculated every time. 

I have went through #82135, and the statement was: "The mentioned behaviour here is not a bug. We are using cached values and if the file contains wrong values there is nothing we can do."

However, I have to disagree. 

For a file that was created, edited and maintained only using LibreOffice Calc, if the cached values are wrong - it should be a problem of LibreOffice, and not the file itself. 

At least the "Always Recalculate" should be turned on by default in LibreOffice Calc.