Get the last value of rows



is there a solution to get the last value of rows as shown in the example provided above?
Rows may contain empty cells.

Thank you very much.

I am not particularly enamoured of this solution as it relies on trying to find 2 in a calculation of 1 divided by an array of 1s (TRUE) which it can’t so it returns the value of the last 1 in the array. Cheers, Al
LastValueInSingleRow.ods (11.2 KB)

Well, that’s to say the least a really nice hack and it does the job!

Thank you very much for that solution!

Argh, sadly it doesn’t work if someone tries to open/modify the file with google spreadsheets.

That was not the question asked and well, spreadsheet is not libreoffice at all :-p but I have a few collaborators using it so if you have an other idea that would work in both?

Spreadsheet message:

There are a few alternatives. I added two of them to the sheet EarnestAl provided. Check mainly column P, but note that the formula there must be explicitly forced to array-evaluation: If you edit it in any way, you need to quit the editing process by Ctrl+Shift+Enter.
(Under “3.” you find a variant not expected to work in MS Excel. Regarding GoogleSheets I don’t know.)
LastValueInSingleRowWithAlternatives.ods (14.8 KB)



Thank you!

Alternative 2 (both with or without helper) works in google sheet (Alt 3 doesn’t), so it solves my initial problem AND will allow others using Google Sheet to update our work.

Once again, thank you both for your replies and your …mastery of these formulas :smiley: