Calc, VLOOKUP 1.1 -> 2.0: Changing of default behaviour?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Calc, VLOOKUP 1.1 -> 2.0: Changing of default behaviour?

Til Schubbe-2
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. 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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc, VLOOKUP 1.1 -> 2.0: Changing of default behaviour?

Andrew Pitonyak
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: [hidden email]
>For additional commands, e-mail: [hidden email]
>
>  
>

--
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc, VLOOKUP 1.1 -> 2.0: Changing of default behaviour?

Til Schubbe-2
* On 13.11. Andrew Douglas Pitonyak ([hidden email]) muttered:

> Til Schubbe wrote:

> >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.

Probably you got me wrong. I meant that asuming an unsorted table is
the default behaviour in Calc 1.1.x.

> http://www.pitonyak.org/database/Calc_As_A_Simple_Database.odt

> 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;

The document you stated referes to Calc 2.x.

> The beahavior changed, I think, in version 2.0.

Yes, that's what I noticed.


To illustrate the problem:

In

http://schubbe.org/test/OOo/vlookup-test.sxc

(created with OOo 1.1.4) you can see an unsorted table (A6:D16) -
you have to open it with OOo 1.1 of course. The cells D1 and E1 are
refering to the unsorted table. Both VLOOKUPs are only called with 3
parameters and the results are correct: D1 shows "c2" and E1 shows
"d11".


In contrast to the 1.1.4 spreadsheet

http://schubbe.org/test/OOo/vlookup-test.ods

(created with OOo 2.0.0) contains the same data and formulars. But D1
shows "c4" and E1 shows "d8". Both results differ (and are wrong)
from the results in the 1.1.4 spreadsheet.


The same formular used in different program versions should either
result in the same outcome. Or the newer program version must
convert the formular (in an older spreadsheet opened with the newer
program version) so that the formular shows the same result as the
spreadsheet in the original (old) version does. Doing neither is a
bug.

Regards
Til

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc, VLOOKUP 1.1 -> 2.0: Changing of default behaviour?

Andrew Pitonyak
Til Schubbe wrote:

>* On 13.11. Andrew Douglas Pitonyak ([hidden email]) muttered:
>
>  
>
>>Til Schubbe wrote:
>>    
>>
>
>  
>
>>>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.
>>    
>>
>
>Probably you got me wrong. I meant that asuming an unsorted table is
>the default behaviour in Calc 1.1.x.
>
>  
>
>>http://www.pitonyak.org/database/Calc_As_A_Simple_Database.odt
>>    
>>
>
>  
>
>>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;
>>    
>>
>
>The document you stated referes to Calc 2.x.
>
>  
>
>>The beahavior changed, I think, in version 2.0.
>>    
>>
>
>Yes, that's what I noticed.
>  
>
When I created the document. I think that I noticed a behavior change
NOT a specification change. In other words, I thought (correctly, or
incorrectly) that it always assumed sorted, but the behavior changed
when an unsorted list was incorrectly referenced as sorted.

>
>To illustrate the problem:
>
>In
>
>http://schubbe.org/test/OOo/vlookup-test.sxc
>
>(created with OOo 1.1.4) you can see an unsorted table (A6:D16) -
>you have to open it with OOo 1.1 of course. The cells D1 and E1 are
>refering to the unsorted table. Both VLOOKUPs are only called with 3
>parameters and the results are correct: D1 shows "c2" and E1 shows
>"d11".
>
>
>In contrast to the 1.1.4 spreadsheet
>
>http://schubbe.org/test/OOo/vlookup-test.ods
>
>(created with OOo 2.0.0) contains the same data and formulars. But D1
>shows "c4" and E1 shows "d8". Both results differ (and are wrong)
>from the results in the 1.1.4 spreadsheet.
>
>
>The same formular used in different program versions should either
>result in the same outcome. Or the newer program version must
>convert the formular (in an older spreadsheet opened with the newer
>program version) so that the formular shows the same result as the
>spreadsheet in the original (old) version does. Doing neither is a
>bug.
>
>Regards
>Til
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: [hidden email]
>For additional commands, e-mail: [hidden email]
>
>  
>

--
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Calc, VLOOKUP 1.1 -> 2.0: Changing of default behaviour?

Niklas Nebel
Andrew Douglas Pitonyak wrote:
> When I created the document. I think that I noticed a behavior change
> NOT a specification change. In other words, I thought (correctly, or
> incorrectly) that it always assumed sorted, but the behavior changed
> when an unsorted list was incorrectly referenced as sorted.

That's right. See issue 2168 for details.

Niklas

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]