Pick last non-empty cell in row of range and use in a calculation

Hi all,

Any help on this issue would be greatly appreciated

I have attached the file which should make things easier.

In the coloums C:Q a user will enter the trial times by each member in the team. (they represent minutes and seconds if it helps)

at any one time, one member may have completed more trials than another, as shown in the spreadsheet.

In column U, I would like to calculate the percentage change between the most recent two trials entered in any row. Im not sure whether this is acheivable via a function or a macro is needed.

In column V, I would like to calculate the percentage change between the first trial entered and the last trial entered.

If any of this can be acheived It would be of great help.

Thanks in advance

Adam

Hi Adam,

(1) Is it safe to assume that there will be no gaps (blank cells) in the data? ie. If Time trial #6 is the last time trial done by Bob, time trial #5 must also have been completed and would have been his previous trial?

(2) In Excel, times cannot be negative. Are you happy to display the absolute time change in columns U and V?

If those are okay then these formulas are an option:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi,

Sorry, I missed the fact that you want percentage changes rather than just the time difference. In that case it's no problem. Format the cells as percentages and then these formulae should do the trick:

Please Login or Register  to view this content.

Please Login or Register  to view this content.