Re: [libreoffice-users] Unexpected result from MATCH() cell function (?)

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 <gurus.knugum@gmail.com
> :

> Den lör 30 mars 2019 kl 16:06 skrev Regina Henschel <
> rb.henschel@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
> <rb.henschel@t-online.de <mailto:rb.henschel@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 without error handling, which I intend to add.

”Please write a bug report.”

I will.

Thanks for your feedback.


Kind regards

Johnny Rosenberg

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy