Display Last Value from End of Row in iWork Numbers

Question

In iWork Numbers (v3.5.2) I would like to display the last value (not int or bool) from a row. I have an image of what I'm looking to do below.

In row 1 I have the Current pulling the value from the latest value, in this case the B under Spring. If possible I would like to be able to leave empty columns at the end of the row and have the Current only update if there's new data.

Is this possible?

Answer 1

I believe what you want (in this case for row 1) is:

INDEX(C2:E2,COUNTIF(C2:E2,"*"))

Here's what it does:

• COUNTIF gives you the number of cells that satisfy a condition. The condition I used was "*" which means any character. So if only two of the cells in C2 to E2 have a value, it will return 2, but if all three are present it will return 3.
• INDEX will return the value of a cell in a range of cells. Our range here is again C2 to E2 and we've said in that range, give me the Xst cell, where X is the number that COUNTIF returns.

All together it's something like "count the number of cells that have something in them, use that number to get me last cell."

You'll have to change the range for each row, but numbers might be able to figure it out if you do a couple.