https://bugs.documentfoundation.org/show_bug.cgi?id=158258
--- Comment #8 from ady <adylo811...@gmail.com> --- 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 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 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. -- You are receiving this mail because: You are the assignee for the bug.
