1

I have the following data sheet. It has a row of dates, where every date has an A and a B value. I would like to find the last filled-in B value for that range.

In the following screenshot, I would like a formula which can calculate the contents of the LAST A column for every row.

Screenshot demonstrating an example data set.

Please note I do not want to merely find the last filled-in cell. For row 5 that would be C5, while the last B-value would be B5, or 6.

Improve this question
1
  • 1
    Note: @Scott Craner posted an answer but deleted it, presumably because his answer was for Excel. Expressions such as =AGGREGATE(14,3,A1:A5*(A1:A5>0),1) do not work in LibreOffice. However =SUMPRODUCT(A1:A5>1) works.
    – Jim K
    Mar 14, 2018 at 16:32

1 Answer 1

Reset to default
1

Enter the following formula normally in G3 and fill down.

=INDIRECT(ADDRESS(ROW();SUMPRODUCT(MAX(COLUMN($A3:$F3)*($A3:$F3<>"")*($A$2:$F$2="A")))))

G3 equals 5

This uses SUMPRODUCT in a tricky but important way. It forces MAX to calculate as an array formula, evaluating each cell in A3 to F3. In this formula, that is its only purpose -- we did not use SUMPRODUCT to add or multiply.

Alternatively, it can be entered as an array formula without SUMPRODUCT. However, array formulas are difficult to work with, so I recommend the first version.

{=INDIRECT(ADDRESS(ROW();MAX(COLUMN($A3:$F3)*($A3:$F3<>"")*($A$2:$F$2="A"))))}

Documentation: Using Arrays, INDIRECT, COLUMN.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .