+ Reply to Thread
Results 1 to 13 of 13

Identifying Record Performance Numbers - Details inside, this one will be fun :)

  1. #1
    Registered User
    Join Date
    05-20-2013
    Location
    detroit, michigan
    MS-Off Ver
    Excel 2003
    Posts
    60

    Identifying Record Performance Numbers - Details inside, this one will be fun :)

    Hey all,

    I've got a (hopefully) fun one for you. I've attached some dummy data: Running.xlsx

    That spreadsheet tracks the distance an individual ran on a given day from 1/1/2014 - 2/28/2014 using two columns: Date (in descending order from 2/28/14 - 1/1/14) and Distance (the number of miles they ran that day).

    What I am trying to see is, for each time the runner hits a new record distance, how well does the runner perform in the three days following the record? Does he hit a new record quickly after setting one? Does he slump and not hit another record for a few weeks??

    For example, this is the data for the first 10 days in the attached spreadsheet:

    Date Distance
    1/10/2014 2.839
    1/9/2014 1.871
    1/8/2014 1.382
    1/7/2014 1.295
    1/6/2014 2.839
    1/5/2014 1.131
    1/4/2014 0.352
    1/3/2014 0.546
    1/2/2014 2.740
    1/1/2014 1.131

    For just that isolated section, this is the type of information I would like to be able to extract:

    Record Date Record Distance Day 1 Post RD Day 2 Post RD Day 3 Post RD
    1/1/2014 1.131 2.740 0.546 0.352
    1/2/2014 2.740 0.546 0.352 1.131
    1/6/2014 2.839 1.295 1.382 1.871

    To be clear as to why the information in the bottom chart was selected, since there was no data prior to 1/1/14, the "Record Distance" is, by default, the distance ran on 1/1/14 - so that information is populated into the second chart. Along with it, we have the distances ran on 1/2, 1/3 and 1/4.

    The next record distance was seen on 1/2/14, so that number is recorded on the next line followed by the distances seen on 1/3, 1/4 and 1/5. The following record distance isn't seen until 1/6, so the distance for 1/6 is recorded as well as 1/7, 1/8 and 1/9.

    While that is all of the records seen in the first ten days of data, if you have the full Excel sheet opened, these are all of the record dates that should eventually be on the bottom chart:

    1/15/2014 - 3.138 (have distance for 1/16, 1/17 and 1/18 also in row)
    1/19/2014 - 3.210 (have distance for 1/20, 1/21 and 1/22 also in row)
    2/15/2014 - 4.435 (have distance for 2/16, 2/17 and 2/18 also in row)

    This is part of a larger analysis project I'm working on, so this data is a very small snippet of a much larger sample size. I want to try and nail down the methodology with a small sample size, however, before applying it to the larger set and seeing what (if any) trends are associated with the runner hitting a new distance record.

    Anyone have any thoughts on how to get this done? Can it be done?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Identifying Record Performance Numbers - Details inside, this one will be fun :)

    I see a potential problem here from a formula perspective. When you find the max in your table (1/2/2014 - 2.740), using the MAX formula, the very next time you make an entry higher than that, that same max formula will return that new high
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-20-2013
    Location
    detroit, michigan
    MS-Off Ver
    Excel 2003
    Posts
    60

    Re: Identifying Record Performance Numbers - Details inside, this one will be fun :)

    Quote Originally Posted by FDibbins View Post
    I see a potential problem here from a formula perspective. When you find the max in your table (1/2/2014 - 2.740), using the MAX formula, the very next time you make an entry higher than that, that same max formula will return that new high
    That was the same issue I was trying to work around as well, though to no avail. In a perfect world, I would be able to start at the bottom and, for any given distance, check and see if any distance below it was greater than that value; and, if so, add that value and the three values above it to the second chart. I feel like I'm verging more into some sort of Macro than what can be accomplished through normal functions, but I don't really know where to draw that line - hence me asking here

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Identifying Record Performance Numbers - Details inside, this one will be fun :)

    hmm on 2ned thoughts, if you are not against using helpers, I think I have something?

    Edit: I just noticed your table is upside down - latest date at the top, earliest at the bottom - are you stuck on having it that way?
    Last edited by FDibbins; 11-01-2014 at 08:09 PM.

  5. #5
    Registered User
    Join Date
    05-20-2013
    Location
    detroit, michigan
    MS-Off Ver
    Excel 2003
    Posts
    60

    Re: Identifying Record Performance Numbers - Details inside, this one will be fun :)

    Quote Originally Posted by FDibbins View Post
    hmm on 2ned thoughts, if you are not against using helpers, I think I have something?

    Edit: I just noticed your table is upside down - latest date at the top, earliest at the bottom - are you stuck on having it that way?
    Helpers? I'm not familiar. And I've got no qualms about having it in ascending or descending order. When I export the data, it's in descending order - though it doesn't really change things to have it in ascending order either.

    I'd love to hear more about what you're thinking, though! I've been stumped on this one all day

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Identifying Record Performance Numbers - Details inside, this one will be fun :)

    OK, I think I have it for you, but this version is based on your dates being in assending order (low to high)

    A helper column is just what it says, it is a column that you can put formulas in to "help" with other calcs - these can be hidden if needed. I added a helper in C to ID the max's, then used another helper to create unique ID for each new "max"

    It then becomes a simple matter to use INDEX/MATCH to pull in the data you want.

    See the attached
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-20-2013
    Location
    detroit, michigan
    MS-Off Ver
    Excel 2003
    Posts
    60

    Re: Identifying Record Performance Numbers - Details inside, this one will be fun :)

    Quote Originally Posted by FDibbins View Post
    OK, I think I have it for you, but this version is based on your dates being in assending order (low to high)

    A helper column is just what it says, it is a column that you can put formulas in to "help" with other calcs - these can be hidden if needed. I added a helper in C to ID the max's, then used another helper to create unique ID for each new "max"

    It then becomes a simple matter to use INDEX/MATCH to pull in the data you want.

    See the attached
    Ah that is awesome! If it's not too much trouble, do you think you could explain a bit about the process / logic behind how this works or point me in the direction of resources that might be able to teach me more? There are a few functions (and uses of those functions) that I haven't seen done like this and would really like to understand the "how" behind this amazing work you did as well as having a finished product.

    Even if you don't, though, this is seriously great and I really appreciate the help. I was going down a rabbit hole that didn't put me close to where you ended up, and this really helped to point me in the right direction in and of itself!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Identifying Record Performance Numbers - Details inside, this one will be fun :)

    Sure, I will explain all I can, and thanks for the kind words

    1. I used a slight variation on the max() to get a running max...
    =MAX($B$2:B2)
    By locking the 1st reference and not the 2nd, as the formula gets copied down, the max range progressively increases...$B$2:B2...$B$2:B3...$B$2:B4 etc
    doing this displays the max up to that specific row

    For the next trick, I create a value that increases each time the "max" value inthe 1st helper increases...
    =IF(C2=C1,D1,D1+1)
    so if "the cell in this row in column C" = "the cell in the row above in column C", take teh value in the cell above (in column D), else add 1 to the value above
    You can see how this works in column D to progressively increase as the max increases

    (Im out of time right now, will explain more in a few minutes)

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Identifying Record Performance Numbers - Details inside, this one will be fun :)

    A slightly different approach. I sorted the dates in order then determined when records were set then gave the % of performance between records for each date.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Identifying Record Performance Numbers - Details inside, this one will be fun :)

    OK, to continue

    (nice approach btw Ron)

    So, we now have a list if progressive max's and their unique ID's (1, 2, 3 etc). Now we need to pull them out.

    I started with the max distance 1st, using this...
    =IFERROR(INDEX($B$2:$B$60,MATCH(ROW(A1),$D$2:$D$60,0)),"")
    The IFERROR(blah-blah,"") is just to error-trap for when there is no data to show

    INDEX works on finding the intersection of a specified row and column...=INDEX(range,row number, column number)
    I used the Distance column as the range, and that will also be the column number (1), so now we just need to ID the row. I did this using the MATCH() function...
    =MATCH(find-what,range to search (single column),0) the 0 is for an exact match

    So I want to find the 1st max, then the 2nd max, the 3rd etc, so I used the ROW() function to give me a progressivly increasing value to search for.

    If you look at teh ROW(A1) part as you have copied it down, you will see it becomes ROW(A2), ROW(A3) etc
    ROW(A1) = 1, ROW(A2) = 2 and so on
    so this...
    MATCH(ROW(A1),$D$2:$D$60,0)
    MATCH stops looking after it finds teh 1st match, so it becomes...
    MATCH(1,$D$2:$D$60,0)...which is D2
    MATCH(2,$D$2:$D$60,0)...which is D3
    MATCH(3,$D$2:$D$60,0)...which is D7
    which in turn gives us the row number to use for the INDEX()

    Now that we have the max values, it's relatively simple to then pull in the matching date for those values - again using the INDEX/MATCH just described.

    For the Post 1, post 2 etc, I just modified the DISTANCE formula to look at 1 row down by adding 1 to the MATCH row found...
    INDEX($B$2:$B$60,MATCH(ROW(A1),$D$2:$D$60,0)+1)
    Then adding 2 etc

    Hope this makes sense?

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Identifying Record Performance Numbers - Details inside, this one will be fun :)

    @Ford The finish to this is entirely inspired by your approach to listing the results and the days that followed.

    @ the originator of the thread: This is not meant as an attempt at "one up-man-ship". It is just a different way of looking at the problem the and the table part inspired entirely by Ford.

    For what it is worth:
    Explanation of the worksheet to show what I was thinking in the development of the worksheet.
    1. I sorted the data into ascending date order.
    2. In column C the following formula was entered and copied down the length of the data to determine if the value in column B was greater than any of the values before. If the value was greater, enter it in column C if not greater leave the cell blank:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    3. In column D the performance between records was calculated by this simple formula that looks for blank cells in column C and when found returns the value in column B otherwise leave a blank cell.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    4.To give a value to the performance after the record, the runs following the record were given as a percentage of the record.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    5. The count of records made, count of runs and runs per record are simple COUNT formulae that should be self-explanatory.
    6. To list the records in the chart in the order in which they were established, I used the SMALL function to find in ascending order the records set. That is why I left blanks in column C:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The only thing a little different about this formula is the use of the ROWS function. This acts as a counter as the formula is copied down the counter will increase by 1 per row thus retrieving the smallest record, second smallest etc as the formula is copied down.
    7. The rest of the formulae in columns H, I, J, and K are pretty much the same as was used by Ford.
    Attached Files Attached Files

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Identifying Record Performance Numbers - Details inside, this one will be fun :)

    Ron, nice touches there (and thanks for the feedback )

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Identifying Record Performance Numbers - Details inside, this one will be fun :)

    @Ford

    You're welcome.

    Thanks for the inspiration to go further.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Files Fetching Details-Workbook Slow performance
    By laxmanann in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-25-2014, 04:33 PM
  2. Randomly Select Worker Based On Eligibility (see details inside)
    By levitt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-23-2014, 03:11 PM
  3. Creating Dynamic Database to Webpage - Details Inside
    By cjm2010 in forum Excel General
    Replies: 0
    Last Post: 02-20-2012, 03:38 PM
  4. Replies: 4
    Last Post: 06-22-2011, 04:36 AM
  5. Identifying last record (de-duping)
    By md1972 in forum Excel General
    Replies: 7
    Last Post: 12-07-2009, 10:12 AM

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