Bug 85078 - LOOKUP cannot handle negative criterion
Summary: LOOKUP cannot handle negative criterion
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-10-16 02:20 UTC by stephen brede
Modified: 2014-12-11 05:10 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
financial spreadsheet where i was looking to find which month had the most negative unreconciled balance. Sheet octiber, cell M2 has offending formula. (64.81 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-16 02:20 UTC, stephen brede
Details

Note You need to log in before you can comment on or make changes to this bug.
Description stephen brede 2014-10-16 02:20:56 UTC
Created attachment 107908 [details]
financial spreadsheet where i was looking to find which month had the most negative unreconciled balance. Sheet octiber, cell M2 has offending formula.

Problem description: LOOKUP doesn't handle negative search criterion correctly.

Steps to reproduce:
1. ....create lookup where search criterion is a negative number.
2. ....
3. ....

Current behaviour:Result of above lookup is "#N/A"

Expected behaviour:same behaviour as if search criterion was a positive value.
Attached spreadsheet October tab Cell M2 contains evidence.
Using any of the adjacent cells for the search criterion gives correct result. however the K2 (negative criterion) results in the error.
I have checked for any spurious data (such as values created by calculation not simple currency input) and there are none except simple addition and subtraction of currency values.
The help file doesn't say that the criterion must be positive.
I am not sure if the help file line " Additionally, the search vector for the LOOKUP must be sorted ascending, otherwise the search will not return any usable results." is the reason but the adjacent lookup cells are not sorted and give the expected result.
Comment 1 ign_christian 2014-10-16 05:52:13 UTC
(In reply to stephen brede from comment #0)
> I am not sure if the help file line " Additionally, the search vector for
> the LOOKUP must be sorted ascending, ...
Possibly not a bug since if we do what is told in Help, sort ascending the search range, it works as expected.

Should be the same with Excel:
http://support2.microsoft.com/kb/181201
http://stackoverflow.com/questions/12812192/lookup-function-in-excel-is-throwing-na-error
Comment 2 Regina Henschel 2014-12-07 13:39:19 UTC
Component: Formula Editor --> Spreadsheet
Comment 3 Robinson Tryon (qubit) 2014-12-10 13:36:49 UTC
(In reply to ign_christian from comment #1)
> (In reply to stephen brede from comment #0)
> > I am not sure if the help file line " Additionally, the search vector for
> > the LOOKUP must be sorted ascending, ...
> Possibly not a bug since if we do what is told in Help, sort ascending the
> search range, it works as expected.

So I see a couple of possibilities here:
1) Make this an enhancement request to add support for negative criterion
2) Make this an enhancement request to the docs to explicitly preclude negative criterion
3) Call the situation "good enough" and Resolve it as NOTABUG

Stephen: As the bug reporter, I'll let you decide which path to choose :-)

Status -> NEEDINFO

(please change status back to UNCONFIRMED after you reply)
Comment 4 GerardF 2014-12-10 13:56:51 UTC
This as nothing to do with negative or positive (even number or text) criterion.

LOOKUP use binary (dichotomic) search.
http://en.wikipedia.org/wiki/Binary_search_algorithm
And for a binary search, search vector must be sorted in ascending order.

It is explicitely written in the help.
Comment 5 stephen brede 2014-12-11 05:10:06 UTC
enhancements would be a great help is situations like mine where i need to find the minimum or maximum value in a list and then have the ability to report the position in the list.
I am not sure if there was a bug originally as i only got errors with negative numbers in the list. Although it was unsorted getting an N/A result was at the least confusing.
Will request an enhancement soon.
Too many other things to do at the moment.
Thanks to all.