A simple formula that compares a large number of cells, decrementing step by step from cell to cell on the left. It’s difficult to expand when adding another column [H:H]. Does anyone know of a less silly formula that doesn’t require manual editing for such an expansion?
000_LO-CALC_observe latest value_123102.ods (31.7 KB)
Yes, and good: I have known and been testing this complex formula since yesterday, with which I can choose between any character or number, and I was looking for a “simpler” one just for numbers.
This is exactly the formula I was looking for, as I had completely forgotten the referenced post.
1 Like
Alternative suggestions:
(I don’t actually like the 1E100 trick much.)
See also
https://bugs.documentfoundation.org/show_bug.cgi?id=167468
and
disask124771_Last_First_DataOrder.ods (36.3 KB)
Why doesn’t you like that?
It works well! Instead of the ultra-number 1E+100, the computer doesn’t have to compare as many bits with the expression (…;MAX(B11:G11)+1;…).
I must have missed something. Where was a formula using MAX() suggested?
My preference is the “inverse order”, in this case “newest left”. That’s how I organise myself the few Calc documents I still use on a regular basis.
Next candidate woult be XMATCH() with searchdirection=-1, but there currently still is the mentioned bug.
The “tricky” solution given by @Sokol92 is also clever, and I didn’t call it inefficient.
1 Like