Unexpected result from MATCH() cell function (?)

Johnny_Rosenberg · March 30, 2019, 2:54pm

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

Regina · March 30, 2019, 3:06pm

Hi Johnny,

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

Johnny_Rosenberg · March 31, 2019, 4:45pm

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:

rb.henschel@t-online.de>:

Hi Johnny,

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

<rb.henschel@t-online.de <mailto:rb.henschel@t-online.de>>:

    Hi Johnny,

     > 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