Formula for latest number from a ROW

pedrogilg wrote:

I would like the table in front of the orange card "current weight" to automatically display, in real time, the latest value I manually add to the purple table under it on ROW "weight (kg)".

You can use XLOOKUP to find the last value, whether or not there are gaps in the data.

=XLOOKUP(REGEX("."),Table 1::2:2,Table 1::2:2,"no entries",2,-1)

This searches from the last cell to the first cell in the search-range and returns the value in the first cell that it finds that has something in it (that . in regex means any character).

More on XLOOKUP here:

XLOOKUP - Apple Support (HK)

This will work also if you decide to arrange your data vertically. Just select the appropriate search-range, will will then be vertical instead of horizontal as in your screenshot.

Use ; instead of , in the formula if your region uses , as the decimal separator.

SG

If there are no gaps in the data, meaning all columns to the left of the "latest" are filled in, below is a formula (assuming the table name is Table 1).

First convert column A to a header column. You should also convert row 1 to a header row.

=OFFSET(Table 1::B3,0,COUNTA(Table 1::3:3)−1)

This formula will not work if you have gaps in your data. You can fill the gaps with something else, like a dash, and the formula will work.

I would say that a table like that should be laid out vertically though. Imagine if you have 100 values, scrolling left-right is much more annoying than scrolling up-down.

If you decide to transpose rows and columns (menu Table), Badunit's formula would become:

=OFFSET(Table 1::B2,COUNTA('weight (kg)')−1,0)

Your next logical step is to select columns A and B and select a scatter plot chart (bottom left in the chooser) to track your progress.

Hi Recycleur,

Congratulations on your advance to Level 3 and your custom avatar!

Regards,

Ian.

I'm glad SGIII posted a much better formula. I must have been in an old-school mindset, before XLOOKUP was a function in Numbers.