Need help replacing Rank formula

GyleMc · November 19, 2023, 5:14am

I previously posted this question and was given the Rank formula, but that now only works if the number I’m comparing is one of the 10 numbers in the range. I need to know where this value falls even if it is not one of the 10 numbers. Rank was “corrected” and no longer works. See below the explanation of why this no longer works (BTW the numbers are not in ascending or descending order, but random:

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.

JohnSUN · November 19, 2023, 6:33am

For the values given in your example, what result would you like to see? Something like 3.78338177994929? What about
=FORECAST(Test_value;RANK(Sequence_range;Sequence_range);Sequence_range)

koyotak · November 19, 2023, 12:17pm

pardon/sorry for my bad englisch conversation/communication

i see a problem in your listed datas/values, that you have writen outside counterwise OR anti-counterwise modus, so not alternative in successive higher OR lower going numbers. that makes one sulution not easy cause i have to combinate μC-programming with logical and math. formulations.
quoted: «BTW the numbers are not in ascending or descending order, but random:»
i will try it but i need much more conditions in short scripted cases!

GyleMc · November 19, 2023, 3:58pm

Here is the formula I’m currently using, but I can no longer use Rank() since they “fixed” it. I’m just looking for the position compared to a group of ten values in random order. The number I’m looking for is a whole number such as the 4th highest value, 7th, etc. The Max() part tells me if it is larger than all the numbers (1st), and the MIN() tells me if it is the 11th or worst value.

=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)))))

The table O4:X15 are the monthly values (12 rows) for the last decade (10 Columns).

GyleMc · November 19, 2023, 5:09pm

This has been resolved by ady in the bug (that was not a bug) report. Here is the simplified formula: =RANK(F20,F20~INDEX(O4:X15,MATCH(EOMONTH(A20,0)-C20+1,A4:A15,0))). The F20~ adds the current value to those returned by the Index(), which in turn eliminated the need for the 2 If() conditions. This function will now return the value from 1 to 11 as the rank of the current total.

koyotak · November 19, 2023, 10:09pm

sorry/pardon for my bad englisch communication/conversation
that’s an very interesting exercise/question to difference in math. modus or (various) µC-programming modi or logical modus

so i try to explain out of my programmers viewpoint to approach one (of many) possible solution:

logical task: µC-programming

int x //variable: -32768…+32767 == „small-integer“ 2 Bytes//
int a_n //listed in table or matrix, /wobei/within/ n == „position number in list“//

cases: //successive read out! - - - cases in CALC: „=IFS()“//
-.1. x == a_n, TRUE ⋎ FALSE (boolean) //== : equal to / ⋎ : logical OR//
-.2. x != a_n, TRUE ⋎ FALSE (boolean) //!= : logical NOT//
-.3. x > a_n, TRUE ⋎ FALSE (boolean) //> : greater than//
-.4. x < a_n, TRUE ⋎ FALSE (boolean) //< : lower than//
-.5. x != int x, ERROR ⋎ NaN //NaN : not a number, but a value how 4.75 decimal//

break; //do not drive into a infinite loop if fault in all above cases!//

for
a_1 = 682
a_2 = 657
a_5 = 636 //switched with another position//
a_4 = 645
a_3 = 654 //switched with another position//
…

// i have switched the position of a_3 to a_5 for testing clockwise ubwards-counting AND anti-clockwise downwards-counting for any failure!//
//
checking listet words/expressions/values a_n to validity:

observe the downwards counting in listed int-values!
try testing in transforming to upwards counting (= standard listing) *]!
search for a deviated counting punched out of curved/lined seriality!
search for a slipped letter or invisible sign or a undesirable character!

*] because some CALC-functions need a positive succession/sequence like: 1,2,3,4,…,9,10,11,…, that can failured while counting: 1, 10, 11, 12, …, 19, 2, 20, 21, …//

math. task:

f(x) = ??? //function to explane/express a infinite curve/line within 2 dimensions of decimal numbers f(x_n) = a_n(x|y)//

//visualize with every diagram program//
1_LibreOffice-CALC_gelistete Werte abfragen nach identisch UND angenähert höher ODER angenähert niedriger_iN ARBEiT-v0000.ods (38.2 KB)
1_LibreOffice-CALC_gelistete Werte abfragen nach identisch UND angenähert höher ODER angenähert niedriger_iN ARBEiT-v0000.pdf (110.1 KB)
something a little crumb you will/can used.

that’s only a tiny look in µC-programming for just as much understand the CALC-operations/functions/algorithms :: do not despair! and formulate any abstrus question into my belly!

much greetings/hello out of Deutschland (Allemand, Германия, germanskyji, Njemsky, …)