Alternative to OFFSET for finding second-to-last entry?

I am a relatively new user of the more advanced Excel formulas, and have often relied on finding solutions online and managing to adapt them to my needs. I'm really struggling with something though, which I thought OFFSET had helped (I copied the formula from a page I found online) but it's too volatile for the amount of data I have in my worksheets. So if anyone can help me, I'd be really grateful. I am going to do an Excel course, but in the meantime...

Let's say I have survey responses that people have given to a particular question. They can choose one of three different answers, and I have used Define Name to give each of those answers a value. I'm also collecting their responses over time - so in column F I have their response on Day 1, in G I have six months, and in H I have 12 months and so on. So if each row is a person, I have maybe five columns available for their response to this same question over time. For some people I might have four responses, for others I might only have two, because not everyone joined my survey at the same time.

eg.PNG

So if I want to calculate the difference between their most recent response and the one before that, I'd ultimately like a formula that will judge if the value of their most recent responses is higher or lower than the value of their previous answer. But to get there, I need a formula that will find the second-to-last entry.

=OFFSET(F5:J5,0,COUNTA(F5:J5)-2,1,1)

As I said, I was using OFFSET, but it is too volatile. I have read about using INDEX with a colon, but I don't understand the syntax well enough to adapt it to my needs.

I'm very sorry if this is a stupid question. I really appreciate any help.

See yellow banner on how to post a workbook.

I think that you are over thinking this.

=COUNTA(F1:J1) will return the number of cells in your row so if no entries =if (COUNTA(F1:J1)=0 , "")

Try this formula =IF(COUNTA(F5:J5)=0,"Empty Row", IF(COUNTA(F5:J5)=1,"Only Entry: "&F5,OFFSET(E5,0,COUNTA(F5:J5)-1) &" Now "&OFFSET(E5,0,COUNTA(F5:J5))))

Most recent

=INDEX($C3:$F3,,COUNTA($C3:$F3))

Previous

=INDEX($C3:$F3,,COUNTA($C3:$F3)-1)

Then reference columns J/K for difference calculation

I would "rank" the responses by assigning a numeric value to each and doing a simple compare.

Frankly i would stick with Offset. it is designed for the job.

If no data then return "Empty Row" in the real world return ""
=IF(COUNTA(F5:J5)=0,"Empty Row",

If One Entry then Return F5
IF(COUNTA(F5:J5)=1,"Only Entry: "&F5,

If more then one entry then COUNTA(F5:J5) tells you the number of entries
OFFSET(E5,0,COUNTA(F5:J5)) Returns your last entry
OFFSET(E5,0,COUNTA(F5:J5)-1) Returns the first from last entry

OFFSET(E5,0,COUNTA(F5:J5)-1) &" Now "&OFFSET(E5,0,COUNTA(F5:J5))))


Building on Johns suggestion. But using the Let function as well

=LET(Entry,F5:J5,IF(COUNTA(F5:J5)=0,"Empty Row",IF(COUNTA(F5:J5)=1,"Only Entry: "&F5,INDEX(Entry,1,COUNTA(Entry))&" Now "&INDEX(Entry,1,COUNTA(Entry)))))

Define Entry as the range F5 to J5
=LET(Entry,F5:J5

If no data then return "Empty Row" in the real world return ""
,IF(COUNTA(F5:J5)=0,"Empty Row",

If One Entry then Return F5
IF(COUNTA(F5:J5)=1,"Only Entry: "&F5,

If more then one entry then COUNTA(F5:J5) tells you the number of entries
Index(Entry,1,COUNTA(F5:J5)) Returns your last entry
Index(Entry,1,COUNTA(F5:J5)-1) Returns the first from last entry
INDEX(Entry,1,COUNTA(Entry))&" Now "&INDEX(Entry,1,COUNTA(Entry)))))