25-month low

[rmwillis1973]

Struggling with this one, as I can't work out the logic.

I need to create a phrase output that says the UK is at a 25-month low.

• Sort from best to worst;
• Rank current results against others in the table;
• Determine if its a positive or negative trend (positive = high, negative trend = low);
• Append how many months since seen a better or worse result.

Thanks for any solutions as I've been scratching my head on this one.

[tcroberts · Accepted solution]

I've managed to get *A* solution, although it's a bit of a brute force one. I've set up a multi-row formula that sets a value 0 or 1 (could be H or L) if the value of the index in the current cell is equal to the minimum of the previous 25 rows. Here are some screenshots for different configurations of the MultiRow Formulat Tool:

Spoiler

If we set the "Values for Rows that Don't Exist" to "0 or Empty", then we will get the correct answer that only the 32nd row is the 25 Month Low. In fact, in general, with this setting only records after 25 will ever be set to 1 (which corresponds to a low). This is because all records above this will still have a 0 for the non-existing records for the purposes of our formula. 

Another option is to set the "Values for Rows that Don't Exist" to "NULL", which gives us the following result:



We can see that some records near the top are also flagged as 25 Month Lows. This is because, since non-existing rows are set to NULL, they are the smallest numeric value encountered in the previous 25 rows. Depending on your use-case this may be a desirable property.

Cheers!