Description: I have the following formula in a spreadsheet: =RANK(F20,INDIRECT("o"&MATCH((EOMONTH(A20,0)-C20+1),A4:A15)+3&":X"&MATCH((EOMONTH(A20,0)-C20+1),A4:A15)+3)) This resolves to =Rank(f20,O9:x9) this equaled 4. This worked in: Version: 7.4.7.2 (x64) / LibreOffice Community Build ID: 723314e595e8007d3cf785c16538505a1c878ca5 CPU threads: 8; OS: Windows 10.0 Build 22000; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-US Calc: threaded Today I downloaded the current older version (7.5.?.?) from the website and instead of the value 4 it returns #N/A. The help shows the comma as a semi-colon, but when I tried that it changes it to a comma. No matter what I tried it wouldn't work. I went back to the 7.4.7.2 version and it resolves correctly again. Steps to Reproduce: 1.I have the following formula in a spreadsheet: =RANK(F20,INDIRECT("o"&MATCH((EOMONTH(A20,0)-C20+1),A4:A15)+3&":X"&MATCH((EOMONTH(A20,0)-C20+1),A4:A15)+3)) This resolves to =Rank(f20,O9:x9) this equaled 4. This worked in: Version: 7.4.7.2 (x64) / LibreOffice Community Build ID: 723314e595e8007d3cf785c16538505a1c878ca5 CPU threads: 8; OS: Windows 10.0 Build 22000; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-US Calc: threaded Today I downloaded the current older version (7.5.?.?) from the website and instead of the value 4 it returns #N/A. The help shows the comma as a semi-colon, but when I tried that it changes it to a comma. No matter what I tried it wouldn't work. I went back to the 7.4.7.2 version and it resolves correctly again. 2. 3. Actual Results: #N/A Expected Results: 4 Reproducible: Always User Profile Reset: No Additional Info: I have the following formula in a spreadsheet: =RANK(F20,INDIRECT("o"&MATCH((EOMONTH(A20,0)-C20+1),A4:A15)+3&":X"&MATCH((EOMONTH(A20,0)-C20+1),A4:A15)+3)) This resolves to =Rank(f20,O9:x9) this equaled 4. This worked in: Version: 7.4.7.2 (x64) / LibreOffice Community Build ID: 723314e595e8007d3cf785c16538505a1c878ca5 CPU threads: 8; OS: Windows 10.0 Build 22000; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-US Calc: threaded Today I downloaded the current older version (7.5.?.?) from the website and instead of the value 4 it returns #N/A. The help shows the comma as a semi-colon, but when I tried that it changes it to a comma. No matter what I tried it wouldn't work. I went back to the 7.4.7.2 version and it resolves correctly again.
The RANK() function was indeed corrected for LO 7.5.3. Please use the "Add an attachment" link to add a sample file. Please be aware that it will be publicly available, so you might want to read https://wiki.documentfoundation.org/QA/Bugzilla/Sanitizing_Files_Before_Submission before attaching it.
Hallo off topic: Why not: =RANK(F20,INDEX($O$4:$X$15;MATCH(EOMONTH(A20,0)-C20+1;$A$4:$A$15;0))
Thanks, that is a much cleaner formula.
(In reply to ady from comment #1) > The RANK() function was indeed corrected for LO 7.5.3. > > Please use the "Add an attachment" link to add a sample file. Please be > aware that it will be publicly available, so you might want to read > > https://wiki.documentfoundation.org/QA/Bugzilla/ > Sanitizing_Files_Before_Submission > > before attaching it. I will attach the file. The formula in question was cleaned up (simplified) thanks to Werner Tietz, but still does not work in the newer version.
Created attachment 190906 [details] Worksheet in question The formula is on page "24 SREC" cell g20
Don't see the note so I'll try again: The formula in question is on page "24 SREC" Cell G20
To simplify the problem... You are searching the RANK() of the numeric value 647 within the data: 682 657 654 645 636 632 612 559 533 500 from higher to lower values. Until LO 7.5.3, the result would be indeed 4, which is incorrect; the value 647 is not really included in the data. That's incorrect according to the ODFF definition of RANK(), so the function was corrected for LO 7.5.3. I would suggest searching for an alternative way (i.e. modified formula) that would correctly calculate the result you want. Or perhaps the corrected RANK() result is what you actually need? Maybe someone at the users mailing list or at https://ask.libreoffice.org might be able to help with that. Either way, this report is Not A Bug.
This comment is off-topic regarding the report itself, but it may help users in search for similar needs regarding the function/formula. Generally speaking, when the searched-for value is not included in the original data, a possible solution could be to add such value to the referenced data. *BEWARE*: this is not a solution for every case, and it can also have negative consequences. This is only a hint, and you are responsible for adapting the formula to your needs and for reviewing the results when the additional value is either: _ lower than the data _ higher than the data _ in-between the min and max values in data _ included in the data _ invalid _ the rank is sorted in the opposite direction (ascending vs. descending). Taking attachment 190906 [details] as example: From the original formula in cell G20: =IF(F20>MAX(INDEX($O$4:$X$15;MATCH(EOMONTH(A20;0)-C20+1;$A$4:$A$15;0)));1;IF(F20<MIN(INDEX($O$4:$X$15;MATCH(EOMONTH(A20;0)-C20+1;$A$4:$A$15;0)));11;RANK(F20;INDEX($O$4:$X$15;MATCH(EOMONTH(A20;0)-C20+1;$A$4:$A$15;0))))) you could add cell F20 (the searched-for value) to the referenced data, making the formula in G20 as follows (note the F20~ for range in RANK()): =IF(F20>MAX(INDEX($O$4:$X$15;MATCH(EOMONTH(A20;0)-C20+1;$A$4:$A$15;0)));1;IF(F20<MIN(INDEX($O$4:$X$15;MATCH(EOMONTH(A20;0)-C20+1;$A$4:$A$15;0)));11;RANK(F20;F20~INDEX($O$4:$X$15;MATCH(EOMONTH(A20;0)-C20+1;$A$4:$A$15;0))))) (Note: I have not modified the above IF()s, since I'm not sure of their intention and they are not necessarily relevant to the RANKing issue.) Again, this is only a generic hint and in no way a complete perfect solution; not for this case nor for any other. As an example of one of several possible caveats, when the searched-for value in attachment 190906 [details] is lower than the minimum in the given data, the result of the modified RANK() is higher than the original amount of (valid) values (11 instead of 10). Changing the above IF()s might also be relevant for such case(s). Other relevant functions that might be of help in similar RANKing cases: COUNTIF, COUNTIFS, IFERROR, IFNA, SUMPRODUCT. HTH.
THANK YOU ady! Adding the value in f20~ to the rank values is exactly what I needed. The IF() functions can be eliminated as they were in case the value was lower or higher than the existing ten numbers. Now that the value is included in the range it returns the 1 or 11 as needed. This simplifies the formula greatly and again, thank you for this solution!!!!!!!!!!!!!!