Unexpected result from MATCH() cell function (?)

classic Classic list List threaded Threaded
3 messages Options
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Unexpected result from MATCH() cell function (?)

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:

For this test I filled the B column with 40 random values 0–9. It turned
out like this:
B1 0
B2 5
B3 1
B4 2
B5 8
B6 9
B7 0
B8 7
B9 1
B10 1
B11 5
B12 4
B13 7
B14 3
B15 0
B16 8
B17 1
B18 0
B19 5
B20 6
B21 1
B22 2
B23 5
B24 4
B25 2
B26 5
B27 3
B28 6
B29 1
B30 8
B31 8
B32 0
B33 5
B34 0
B35 0
B36 6
B37 6
B38 8
B39 3
B40 0
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)
The expected result would be 40, wouldn't it? In line 40 we find 0, which
is lower or equal to 1 and it should be the last value that is.
Instead the result is 10 and I can't for my life figure out why. There's
absolutely nothing special with line 10. It's not the first 1, it's not the
last. The first is found at line 3 and the last one in line 29. The only
thing I can find that gives me the number 10, is counting to the first 1
from the end, starting at 0 (B40=line 0, B39=line 1 and so on), but that's
not it, because if I now enter 1 in B35 the result is still 10.

Also, if I use Type=-1, ”the first value that is larger or equal is returned”,
I only get #MISSING. Should be 5 (found in B3) or, if they mean ”the index
of the first value that is larger or equal is returned”, 2.

What am I missing?

LibreOffice Calc 6.2.1.2.
Manjaro 18.0.4

Kind regards

Johnny Rosenberg

--
To unsubscribe e-mail to: [hidden email]
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
Regina Henschel Regina Henschel
Reply | Threaded
Open this post in threaded view
|

Re: Unexpected result from MATCH() cell function (?)

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

Kind regards
Regina

--
To unsubscribe e-mail to: [hidden email]
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
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]
>:

> Den lör 30 mars 2019 kl 16:06 skrev Regina Henschel <
> [hidden email]>:
>
>> 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
> <[hidden email] <mailto:[hidden email]>>:
>
>     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: [hidden email]
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