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.knu...@gmail.com >: > Den lör 30 mars 2019 kl 16:06 skrev Regina Henschel < > rb.hensc...@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.hensc...@t-online.de <mailto:rb.hensc...@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+unsubscr...@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
