+ Reply to Thread
Results 1 to 3 of 3

Historical Data

  1. #1
    Registered User
    Join Date
    01-01-2010
    Location
    london, england
    MS-Off Ver
    Excel 2007
    Posts
    26

    Historical Data

    Hi,

    Hoping someone can help me with a question - noting I am not an Excel expert!

    The attached screenshot shows a spreadsheet with a counter (in seconds) which starts when data is linked to the spreadsheet (3rd party medical software). Counter is in cell B1.

    When data is fed to the spreadsheet, the value in B5 and B7 changes continuously over time. B5 and B7 indicate two different data values that change over time.

    I need to capture historical data for 43 seconds when the data feed is activated. For example, when the timer is on 00:00:01, I need E3 to capture the current value in B5. Then when the timer reads 00:00:02, I need E4 to capture the current value in B5. This will continue until 43 seconds worth of data is obtained. This is for the data held in cell B5. The same needs to apply for the data held in cell B7 and captured in cells H3 to H45 accordingly.

    Any help will be greatly appreciated!!!!

    Many thanks,

    Nick

    spreadsheet.jpg
    Last edited by nickm84; 05-07-2013 at 07:53 AM.

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Historical Data

    Hi -

    I'm not really an expert on data capture, but maybe the attached will help. Half of the programming is mock up to simulate incoming data. Ignore the stuff in Column C as those are just incrementing values to increment the timer by 1 second and increment the value in B5 by 5 every time you press the F9 key to simulate incoming data. Also, the Reset is a tool to reset the values back to 0 if you want to restart the simulation. You may or may not want to keep that.

    OK - all the qualifiers out of the way, here's how it works: It checks the time in each cell of Column E versus the time in cell B1. If it's a match, then the corresponding cell in F takes on the value of cell B5. If it's not a match, the F cells reference themselves. This is a circular reference that changes the value in F is the match is true, but holds the value in F the rest of the time. You will need to go into your Excel Options/Formulas/ and select Enable Iterative Calculation. Also set the maximum Iterations to 1.

    You can use the same formula to capture the second set of data.

    One concern I have is whether under an actual incoming data stream is if the time in B1 will actually exactly equal the numbers in column E. In other words, if the time in B1 is 00:00:012 (or 1.2 seconds) rather than exactly :01 I'm not sure if it will be a match. You will just have to try it and see. There may be a way to use the ROUND function if it doesn't work to round to the nearest second.

    Hope this helps.
    Attached Files Attached Files
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Registered User
    Join Date
    01-01-2010
    Location
    london, england
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Historical Data

    Hi,

    Thank you very much for this - much appreciated and so far it seems to work!

    I will mark this as closed but hope I can still access the post in the future.

    Many thanks,

    Nick

    Quote Originally Posted by loginjmor View Post
    Hi -

    I'm not really an expert on data capture, but maybe the attached will help. Half of the programming is mock up to simulate incoming data. Ignore the stuff in Column C as those are just incrementing values to increment the timer by 1 second and increment the value in B5 by 5 every time you press the F9 key to simulate incoming data. Also, the Reset is a tool to reset the values back to 0 if you want to restart the simulation. You may or may not want to keep that.

    OK - all the qualifiers out of the way, here's how it works: It checks the time in each cell of Column E versus the time in cell B1. If it's a match, then the corresponding cell in F takes on the value of cell B5. If it's not a match, the F cells reference themselves. This is a circular reference that changes the value in F is the match is true, but holds the value in F the rest of the time. You will need to go into your Excel Options/Formulas/ and select Enable Iterative Calculation. Also set the maximum Iterations to 1.

    You can use the same formula to capture the second set of data.

    One concern I have is whether under an actual incoming data stream is if the time in B1 will actually exactly equal the numbers in column E. In other words, if the time in B1 is 00:00:012 (or 1.2 seconds) rather than exactly :01 I'm not sure if it will be a match. You will just have to try it and see. There may be a way to use the ROUND function if it doesn't work to round to the nearest second.

    Hope this helps.

+ 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