[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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

References:
[libreoffice-users] Unexpected result from MATCH() cell function (?)Johnny Rosenberg <gurus.knugum@gmail.com>
Re: [libreoffice-users] Unexpected result from MATCH() cell function (?)Regina Henschel <rb.henschel@t-online.de>
Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.