+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    06-02-2010
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    96

    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
    Attached Files Attached Files
    Last edited by a.mack123; 03-31-2011 at 10:04 AM.

  2. #2
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

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

    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.
    Last edited by Colin Legg; 03-31-2011 at 11:28 AM.
    Hope that helps,

    Colin

    RAD Excel Blog

  3. #3
    Registered User
    Join Date
    06-02-2010
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    96

    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. #4
    Registered User
    Join Date
    06-02-2010
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    96

    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. #5
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    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. #6
    Registered User
    Join Date
    06-02-2010
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    96

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

    thank you soo much for your help, works perfectly

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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