Til Schubbe wrote:
Hello,
the last parameter of VLOOKUP tells Calc if the referenced table
is sorted.
I have a Calc document (OOo 1.1.4) which contains some VLOOKUP (in
the German version SVERWEIS) functions passing 3 parameters to it.
The referenced table is unsorted. The default behaviour of VLOOKUP
for omitting the 4th parameter is to asume an unsorted table.
Huh? No it is not. The default behavior is 1, sorted. How do I know
this? Ummm, well, ummm, I do not remember how I know this. I might have
read the source code, but I do not remember. I documented the behavior here:
http://www.pitonyak.org/database/Calc_As_A_Simple_Database.odt
Search for "VLOOKUP". I state the following:
The final column, sort_order, is optional. The default value for
sort_order is 1, which specifies that the first column is sorted in
ascending order; a value of 0 specifies that the data is not sorted. If
the data is sorted in ascending order, a more efficient search routine
is used. A non-sorted search requires an exact match, but a sorted
search always returns a value if the searched text lies between the
first and last values. Table 8 contains examples using the VLOOKUP function.
The beahavior changed, I think, in version 2.0. I had a discussion with
the developers about this. The bottom line is that if the data is NOT
sorted, then the returned value is arbitrary and potentially wrong. I
think that they changed the sorting algorithm in 2.0 to be more
efficient but less tolerant of out of order data. I would elaborate, but
it is late and I musts get to bed!
This
works fine in 1.1.4. Opening the same document with 2.0 (Debian
unstable 2.0.0-1) leads to weird results.
I added a 0 as 4th parameter to VLOOKUP for testing and got the
correct results.
In 2.0 the default behaviour of VLOOKUP (with 3 parameters)
to asume an (un)sorted table seems to have changed. Can someone
confirm this?
If the default behaviour has changed, the function in 2.0 which
converts v1.1 documents into v2.0 documents for initial opening must
add a 0 as 4th parameter to every VLOOKUP function only containing 3
parameters.
Shall I report a bug?
Regards
Til
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
My Book: http://www.hentzenwerke.com/catalog/oome.htm
Info: http://www.pitonyak.org/oo.php
See Also: http://documentation.openoffice.org/HOW_TO/index.html
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
