I am using LibreOffice V7.2.5.2 - I am somewhat of a novice.
I am trying to select the “bottom-most” value from a column of values that change monthly.
I have tried using formulae suggested within these forums and they either display nothing, display an error, default “;” to “,” in the formula or are not what I am attempting to display.
In the image below I want to populate the latest updated value in Col B in F1. Simple?
Hopefully the copy of the spdsht below is self explanatory. Any help appreciated,
Welcome!
What about
=INDEX($F$2:$F$1000;SUMPRODUCT(MAX(IF($F$2:$F$1000="";"";ROW($F$2:$F$1000))))-1)?
1 Like
Thanks John, appreciate your quick response. Could you explain the formula for me in detail pls?
I am assuming $F$2:$F$1000 should be $B$3:$B$1000 in my sheet (Col B being the values that change monthly. Therefore the other “F” values should be changed to “B”?)
Have tried this but I get ERR:509
Any ideas?
THANKS PKG, however, utilising your formula it returns the value at B1000, ie. nothing! If I put a value in B1000 it returns only that value. This is confusing in the least.
I notice that the “;” (semi-colon) in the formula defaults to “,” (comma) when run.
Could this be possibly a version error or idiosyncrasy??
Any ideas anyone?
@woodywazza, the language of pictures is not very convenient for communication.
Please upload example file, then communication will become more productive.
With some difficulty, I achieved the same behavior of this wonderful formula - for this I had to turn all the numbers in the column into text. And with numerical values the formula =LOOKUP(MAX(B3:B1000)+1;B3:B1000) works great!
Try pressing Ctrl+F8. What color are the values in your column B? Remained black? This is a bad sign - your values are not usable - they are not numbers, they are text strings that look like numbers. You will have to take some steps to convert the values in these cells.
You can leave it as is. But it will be better if you change this option in Tools - Options - LibreOffice Calc - Formula - Separators
Last value
=LOOKUP(2;1/(NOT(ISBLANK(B$1:B$9999)));B$1:B$9999)
Last row
=LOOKUP(2;1/(NOT(ISBLANK(B$1:B$9999)));ROW(B$1:B$9999))
Use ; when posting on international sites, as there are areas in this world, where your(?) english 2.1 is written as 2,1 - so language settings could change the interpretation of 2,1 from 2 parameters to one floating point. If you are lucky this results in an instant error, but for BASIC you can’t be sure…
1 Like
Thank you Miguel for this solution. All is now good and I have learned a lot from this exercise.