I did it again, I replied directly to someone who replied instead of
replying to the list, sorry for that. It happens all the time, I just don't
seem to get used to do it right…
Here it is:
Den lör 30 mars 2019 kl 20:30 skrev Johnny Rosenberg :
> Den lör 30 mars 2019 kl 16:06 skrev Regina Henschel <
> rb.hensc...@t-online.de>:
>
>> Hi Johnny,
>>
>> Johnny Rosenberg schrieb am 30-Mar-19 um 15:54:
>> > Hi!
>> >
>> > Maybe I just don't understand how this works, so I write her for help
>> > determining if this is a bug or not before filing a bug report:
>> [..]
>> > Now I use the Match function to look using Type=1, ”the index of the
>> last
>> > value that is smaller or equal to the search criterion is returned”. The
>> > formula looks like this:
>> > =MATCH(1;B1:B40;1)
>> [..]
>> >
>> > What am I missing?
>>
>> see help, "If Type = 1 or if this optional parameter is missing, it is
>> assumed that the first column of the search array _is sorted_ in
>> ascending order."
>>
>
> Ok, thanks. That was definitely not what I read at
> https://help.libreoffice.org/Calc/Spreadsheet_Functions#MATCH:
> ”If Type = 1 or the third parameter is missing, the index of the last
> value that is smaller or equal to the search criterion is returned. *This
> applies even when the search array is not sorted*. For Type = -1, the
> first value that is larger or equal is returned.”
> Even my local help file,
> file:///usr/lib/libreoffice/help/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3158407,
> says the same thing.
>
> So obviously the documentation needs to be synchronised slightly… Or even
> better, if the actual function was changed so it matched the files above,
> that would be very great. I'm working on something that really needs this
> (for now I'm using a cell function that I had to create myself to overcome
> this shortcoming).
>
>
> Kind regards
>
> Johnny Rosenberg
>
> And the response I got from that was:
Hi Johnny,
Johnny Rosenberg schrieb am 30-Mar-19 um 20:30:
> Den lör 30 mars 2019 kl 16:06 skrev Regina Henschel
> mailto:rb.hensc...@t-online.de>>:
>
> Hi Johnny,
>
> Johnny Rosenberg schrieb am 30-Mar-19 um 15:54:
> > Hi!
> >
> > Maybe I just don't understand how this works, so I write her for
help
> > determining if this is a bug or not before filing a bug report:
> [..]
> > Now I use the Match function to look using Type=1, ”the index of
> the last
> > value that is smaller or equal to the search criterion is
> returned”. The
> > formula looks like this:
> > =MATCH(1;B1:B40;1)
> [..]
> >
> > What am I missing?
>
> see help, "If Type = 1 or if this optional parameter is missing, it is
> assumed that the first column of the search array _is sorted_ in
> ascending order."
>
>
> Ok, thanks. That was definitely not what I read at
> https://help.libreoffice.org/Calc/Spreadsheet_Functions#MATCH:
> ”If Type = 1 or the third parameter is missing, the index of the last
> value that is smaller or equal to the search criterion is returned.
> *This applies even when the search array is not sorted*. For Type = -1,
> the first value that is larger or equal is returned.”
Indeed, I see *This applies even when the search array is not sorted*
too. I had not noticed it before. But it was never true. OOo1.1.5 gives
an error, if you try Type=1 on an unsorted array and since OOo2 it
behaves as LibreOffice today. I'm not sure about old StarOffice.
*This applies even when the search array is not sorted* cannot work,
because not a linear but a binary search is used.
> Even my local help file,
>
file:///usr/lib/libreoffice/help/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3158407,
> says the same thing.
>
> So obviously the documentation needs to be synchronised slightly…
"synchronised" is not enough, it seems to be wrong in all cases. Please
write a bug report.
Kind regards
Regina
>
>
>> Kind regards
>> Regina
>>
>
So here we are, everything brought back to the list. I have some more
thoughts about this though:
”cannot work, because not a linear but a binary search is used”
So would it be impossible to make the developers rewrite the function from
scratch using a linear search? I know it would be slower, but wouldn't it
be very much more useful? I mean, what the function actually does must be
more important than how it does it, right? To me it sounds like a binary
search is generally a good idea, but not in this specific case, since we
want to be able to use the function on a non-sorted list (or don't we?).
It can't be that hard to rewrite it. A linear search is probably less
complicated than a binary one (I guess ”binary” in this case refers to a
binary tree, but maybe I misunderstood the whole thing as usual).
But maybe this is the wrong forum for these things.
I solved my issue by just writing my own function, a simplified one only
useful for my particular need in this very case. I got away with eleven
lines but that's
