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

[libreoffice-users] 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: 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

Follow-Ups:
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.