I have a table in Numbers with a row for each person with a header with consecutive days of the year (every day is represented) and starting at the END of each row, I want to find out how many days since the last time the number has changed.

I first misinterpreted your ask, since I assumed you wanted the sheet to track when the cell was changed, but in looking closer to your screenshot, I think what you want is for the cells to react to the adjacent cells, which is do-able, with some caveats.

Digging it, it seems that you have two colors in mind depending on whether the value (score?) went up or down compared to the previous day.

I was able to replicate this using a snapshot of your data:

except for the first row, but I'll get to that.

The key is conditional formatting.

I did this by selecting the B2 cell - i.e. the second score in the first (non-header) row (the '12' on 12/21) - and setting the conditional highlighting (Inspector -> Cell -> Conditional Highlighting) as:

Here there are two rules. If this cell is greater than A2 (the score went up) then set the format to Blue Fill.

Secondly, if the cell is less than A2 (the score went down) it sets the format to Green Fill.

This is why it can't apply to the first column, since there are no preceding values to compare against.

Next, copy this cells format across all date columns, and down every row (this will clear any existing values, so make sure you have a copy of the scores that you can paste back in afterwards).

> But you don't seem to understand what I'm asking.

You're right - I didn't wean what you were trying to do from your original post. I was focussing on the highlighting not the offset calculation.

Now that you've added context it's a little clearer. Not sure what you want/expect to do with this value, but that's not my problem :)

Assuming you're going to add another column to track this value (it has to go somewhere), I achieved it using the following formula in a new column to the right of the data:

=COLUMN(H2)−XMATCH(REGEX("^(?!"&H2&")"),A2:H2,2,−1)

This is based on my sample data where the last 'date' was in column H. You will need to adjust the references to match the columns where your data is.

The logic behind my formula (and there may well be other ways of achieving it) are:

=COLUMN(H2)

calculates the column number of the last column of figures - this is what you'll need to change to match your data. In my case it returns 8, because I'm using columns 1-8 for the data

=XMATCH(REGEX("^(?!"&H2&")"),A2:H2,2,−1)

This somewhat complicated formula breaks down further into two parts.

First,

REGEX("^(?!"&H2&")")

takes the value in H2 and builds a REGEX() string that looks like:

"^(?!15)"

In the world of regular expressions, this is interpreted as "Not the number 15" (where '15' is the current value of my cell H2)

This is passed into XMATCH() which performs a lookup, looking for 'not 15' in the range of cells that cover this person's scores (I'm using A2:H2, and you'll need to amend that to match your table).

The 2 at the end tells XMATCH to perform a Regular Expression search (otherwise it will look for the literal ^, (, ?, ! characters), and the -1 tells it to perform last-to-first search (i.e. return the last match rather than the first match).

Ultimately, this XMATCH() returns the number of the last column that has a value different from that in H2. This can be subtracted from the column number of H2 itself, and gives you the number you're looking for.

N/A