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

1. ## 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.

2. ## Re: Pick last non-empty cell in row of range and use in a calculation

(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.``

3. ## Re: Pick last non-empty cell in row of range and use in a calculation

Thanks for the quick reply Colin, you are correct to assume there will be no blank cells.

However, in columns U and V i need the percentage to either be positive or negative,

ie if the time gets faster the percentage improvement should be positive and negative if slower.

Thanks

4. ## Re: Pick last non-empty cell in row of range and use in a calculation

Maybe I should change the format of the trails to decimals ie 2.14, 3.01 etc

would that help?

or can we get around it another way?

5. ## Re: Pick last non-empty cell in row of range and use in a calculation

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.``

6. ## Re: Pick last non-empty cell in row of range and use in a calculation

thank you soo much for your help, works perfectly

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1