Historical Data

[nickm84]

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

[loginjmor]

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.

[nickm84]

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

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.