Elapsed Time
Roberto Diego
Does any one knows how to "set up" a "chronometer". I have two columns of
numbers; the left one changes dinamically via DDE, the right one is a
constant. I need to record the amount of time the left cell contains a numer
< constant (that is, the cell to it愀 right). But warning the condition
could hold true several times in a period (say 1 hour) and what I really
want to measure is the total amount of time the condition was true for that
period.
Ideally i would like to have in my spreadsheet three buttons:
Satart all "chronometers"
Stop all "chronometers"
Reset all "chronometers"
Any hints will be greately appreciated
Roberto Diego
Chip Pearson
I do not think that this can be done. I hope that someone else will reply,
proving me wrong, but I do not believe that this is possible. The primary
reason is that you cannot predict how often Excel will recalculate the
worksheet, let alone recalculate that particular cell. In general, an Excel
worksheet exists as a "snapshot" of the current values of the cells. It
does not "remember" the previous state of the worksheet, nor does it track
when changes were made to the various values of the cells.
Cordially,
Chip Pearson
http://home.gvi.net/~cpearson/excel.htm
Roberto Diego wrote in message ...
Peter Beach
It is not a terribly nice solution, but it is possible to do it using a User
Defined Function. This implementation assumes that you have one column of
data values (your DDE links) with another column of "target" values. Copy
the code below into Module1.
Assuming your data and target values are in cells a1:a10 and b1:b10, select
the range c1:c10 and enter in the array formula:
=Chrono1(a1:a10,b1:b10)
The array should fill with zero's, but whenever the worksheet recalculates
the function will report the total amount of time (in this Excel session)
the actual value has equalled the target value. Time is reported in hours.
The code to paste into Module1 is:
Option Explicit
Private l_daTimeKept() As Double
Private l_daStartTime() As Double
Private l_baWasMatched() As Boolean
Public Function Chrono1(rdata As Range, rtarget As Range) As Variant
' ----------------------------------------------------------------------
' DESCRIPTION: This array formula will return an array containing
' the amount of time each cell in rdata was equal to
' rtarget. Time is expressed in hours
' ----------------------------------------------------------------------
Application.Volatile
Dim nTop%
Dim i%
Dim OutArr() As Double
nTop% = -1
On Error Resume Next
nTop% = UBound(l_daTimeKept)
On Error GoTo 0
If rdata.Rows.Count <> rtarget.Rows.Count Then
Chrono1 = 0
Exit Function
End If
If rdata.Rows.Count > nTop% + 1 Then
nTop% = rdata.Rows.Count - 1
ReDim Preserve l_daTimeKept(nTop%) As Double
ReDim Preserve l_daStartTime(nTop%) As Double
ReDim Preserve l_baWasMatched(nTop%) As Boolean
End If
ReDim OutArr(nTop%) As Double
For i% = 0 To nTop%
If rdata.Cells(i% + 1, 1) = rtarget.Cells(i% + 1, 1) Then
If Not l_baWasMatched(i%) Then
l_daStartTime(i%) = Now
l_baWasMatched(i%) = True
End If
ElseIf l_baWasMatched(i%) Then
l_baWasMatched(i%) = False
l_daTimeKept(i%) = l_daTimeKept(i%) + Now - l_daStartTime(i%)
End If
If rdata.Cells(i% + 1, 1) = rtarget.Cells(i% + 1, 1) Then
OutArr(i%) = 24 * (l_daTimeKept(i%) + Now - l_daStartTime(i%))
Else
OutArr(i%) = 24 * l_daTimeKept(i%)
End If
Next i%
Chrono1 = Application.Transpose(OutArr)
End Function
The function will only update when the sheet is recalculated so to get
up-to-date readings you will have to press F9 (unless your DDE links are
frequently updating!)
HTH
Peter
Harald Staff
(Spreadsheet with code and buttons installed e-mailed)
I believe this will work, unless the repeatingly running macro disturbes
the data refreshment. Timing if A1:A200<B1:B200, writing time in E:
--------------
Dim tim As Boolean 'on top of module
Sub startit()'assign start button
tim = True
With Worksheets(1)
.Cells(1, 7).NumberFormat = "hh:mm:ss"
.Cells(1, 7).Value = Now - Int(Now)
.Cells(1, 6).Value = "Last checked"
.Cells(1, 8).Value = "Start"
.Cells(1, 9).NumberFormat = "hh:mm:ss"
.Cells(1, 9).Value = Now - Int(Now)
End With
gotimer
End Sub
Sub gotimer()
With Worksheets(1)
If tim = False Then Exit Sub
For R = 1 To 200
.Cells(R, 5).NumberFormat = "hh:mm:ss"
If .Cells(R, 1).Value < .Cells(R, 2).Value Then
If .Cells(R, 5).Value = "" Then
.Cells(R, 5).Value = 0
Else
.Cells(R, 5).Value = .Cells(R, 5).Value + _
Now - Int(Now) - .Cells(1, 7).Value
End If
End If
Next
.Cells(1, 7).Value = Now - Int(Now)
End With
Application.OnTime (Now) + _
TimeSerial(0, 0, 30), "gotimer"
End Sub
Sub stopit()'assign stop button
tim = False
End Sub
Sub resetit()'assign reset button
Worksheets(1).Range("E1:E200").ClearContents
Worksheets(1).Cells(1, 7).Value = ""
Worksheets(1).Cells(1, 9).Value = ""
End Sub
------------------
Best wishes Harald
Stephen Bullen
> I do not think that this can be done.
And I thought people had stopped saying that phrase. I guess this is another
entry for the "They Said it Couldn't be Done" section of my web page <g>.
> I hope that someone else will reply,
> proving me wrong, but I do not believe that this is possible.
OK, see below <g>
> The primary
> reason is that you cannot predict how often Excel will recalculate the
> worksheet, let alone recalculate that particular cell. In general, an Excel
> worksheet exists as a "snapshot" of the current values of the cells. It
> does not "remember" the previous state of the worksheet, nor does it track
> when changes were made to the various values of the cells.
While this is true in general, and you won't find it documented in any of the
Excel books, you can quite easily give spreadsheets 'memory' using circular
reference calculations. For example, you can have a cell with a formula:
=IF(<trigger>,<new value>,<myself>)
When the trigger is True, the cell takes the new value. When the trigger is
false, the cell remembers its previous value. This simple case can be
extended to create a for-next loop (using two cells):
A1: =IF(<trigger to start>,<Stop Value>,0)
B1: =IF(A1=0,<Start Value>,MIN(A1,B1+<Step Value>))
Cell B1 is then the 'variable' in the for-next loop, which can itself be the
trigger for other calculations.
For some examples, download lastchng.zip and previous.zip from my web site.
Anyway, on with the actual problem. We'll be using lots of circular
references, so go to Tools, Options, Calculation and enable Iteration, with at
least 3 'Max Iterations' (the actual number doesn't matter).
Firstly, we need two cells to reset and start/stop the chronometers, so:
A1: "Reset"
B1: 0
A2: "Running"
B2 0
If the reset flag is zero, all our watches will be zeroed. When set to 1,
they'll do their counting. If the Running flag is zero, the clocks won't do
any counting. If set to 1, they'll do their stuff.
Now type (copy/paste) the following formulae in the following cells:
A4: "DDE Link"
A5: 450
B4: "Threshold"
B5: 500
C4: "Calculation Trigger"
C5: =IF($B$1=0,0,IF(OR(A5<>F5,$B$2<>I5),2,0))
D4: "Iteration Count"
D5: =IF($B$1=0,0,MIN(C5,D5+1))
E4: "Previous Value"
E5: =IF($B$1=0,0,IF(D5=1,F5,E5))
F4: "Current Value"
F5: =IF($B$1=0,0,IF(D5=2,A5,F5))
G4: "Time Last Changed"
G5: =IF(I5=0,NOW(),IF(D5=1,H5,G5))
H4: "Time This Change"
H5: =IF(OR($B$1=0,$B$2=0,D5=2),NOW(),H5)
I4: "Stopping?"
I5: =IF(D5=2,$B$1*$B$2,I5)
J4: "Chrono"
J5: =IF($B$1=0,0,J5+IF(AND(D5=2,E5<B5),H5-G5,0))
Briefly:
Columns C and D identify when to start calculating and use a for-next loop to
provide a 2-step calculation (i.e. 2 iterations, allowing us to specify the
order in which the calculations are done)
Columns E and F use the 2-step calc to 'remeber' the previous and current
values from column A
Columns G and H store the times of the previous and current changes
Column I makes sure we update the chrono when we stop it
Column J stores the elapsed time.
The elapsed time is updated whenever the value in column A changes, or when
the "Running" flag is changed (i.e. the chronos are started/stopped).
Set Max Iterations to 1 to step through the calculation cycle to see how it
works.
Maybe I should write an article (or book) on this someday <g>.
Regards
Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLtd.co.uk
Stephen Bullen
Harald Staff
So "they" is >0 ?
Harald ;-)