Google Sheets - Nested If/Not Blank to Fill a Value

Question

I have 6 columns for recording scores of clients.

• Column N: Intake
• Column O: 3 Months
• Column P: 6 Months
• Column Q: >6 Months
• Column R: Discharge/Drop
• Column S: Comparison Score*

There will always be an Intake score. I seek help with a formula to make the value in Column S default to the latest available score. I'm using that column along with the Intake column score to calculate the percent change in another column.

For example, lets say I have a new client and only an Intake score. I then want the Intake score value in Column N to also populate in that rows Column S. In another example, lets say I have a rating for all columns including discharge/drop, then I want Column R's value to populate in Column S.

Could someone help me figure out a formula to do this? Currently if a score is not available, that cell is blank.

Answer 1

Maybe worth trying:

=filter(N2:R2,column(N:R)=max(filter(column(N:R),(isnumber(N2:R2)))))

in S2 and copied down to suit.