How do I record new Highest / Lowest numbers as data changes in ce
 

Hi, I'm quit new to using excel (07) but have so far been very impressed by
both it's features and it's ease of use - However I have stumbled upon a
problem that I cannot get around - PLEASE help me if you can...

I have a formula that returns a numeric value to a specific cell, Say A1,
this number will always be in the range of 0 to 1,000. A number of different
variables influence what the returned value is and this value changes
approximately 5 times per second. I want to be able to show the highest and
lowest value achieved in cells B1 (highest) and B2 (lowest) and have these
two cells both start out at 0 but auto update as a new high or low is
returned in A1

Any ideas on how I can achieve this or where I can go to find an answer
would be great.

Many Thanks,

Mike.

How do I record new Highest / Lowest numbers as data changes in ce
 

In B1 enter -1
In B2 enter 1001

In the worksheet code area enter:

Private Sub Worksheet_Calculate()
Set a1 = Range("A1")
v = a1.Value
Set b1 = Range("B1")
Set b2 = Range("B2")
Application.EnableEvents = False
If v b1.Value Then
b1.Value = v
End If

If v < b2.Value Then
b2.Value = v
End If
Application.EnableEvents = True
End Sub


--
Gary''s Student - gsnu200802


"Highbury 1" wrote:

Hi, I'm quit new to using excel (07) but have so far been very impressed by
both it's features and it's ease of use - However I have stumbled upon a
problem that I cannot get around - PLEASE help me if you can...

I have a formula that returns a numeric value to a specific cell, Say A1,
this number will always be in the range of 0 to 1,000. A number of different
variables influence what the returned value is and this value changes
approximately 5 times per second. I want to be able to show the highest and
lowest value achieved in cells B1 (highest) and B2 (lowest) and have these
two cells both start out at 0 but auto update as a new high or low is
returned in A1

Any ideas on how I can achieve this or where I can go to find an answer
would be great.

Many Thanks,

Mike.

How do I record new Highest / Lowest numbers as data changes in ce
 

I couldn't get this to work in 2003 until I amended the Private Sub to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

everything else the same

very useful Gary's student

edvwvw

Gary''s Student wrote:
In B1 enter -1
In B2 enter 1001

In the worksheet code area enter:

Private Sub Worksheet_Calculate()
Set a1 = Range("A1")
v = a1.Value
Set b1 = Range("B1")
Set b2 = Range("B2")
Application.EnableEvents = False
If v b1.Value Then
b1.Value = v
End If

If v < b2.Value Then
b2.Value = v
End If
Application.EnableEvents = True
End Sub

Hi, I'm quit new to using excel (07) but have so far been very impressed by
both it's features and it's ease of use - However I have stumbled upon a
[quoted text clipped - 14 lines]

Mike.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200809/1

How do I record new Highest / Lowest numbers as data changes i
 

Thanks so much for that, works like a charm....

"Gary''s Student" wrote:

In B1 enter -1
In B2 enter 1001

In the worksheet code area enter:

Private Sub Worksheet_Calculate()
Set a1 = Range("A1")
v = a1.Value
Set b1 = Range("B1")
Set b2 = Range("B2")
Application.EnableEvents = False
If v b1.Value Then
b1.Value = v
End If

If v < b2.Value Then
b2.Value = v
End If
Application.EnableEvents = True
End Sub


--
Gary''s Student - gsnu200802


"Highbury 1" wrote:

Hi, I'm quit new to using excel (07) but have so far been very impressed by
both it's features and it's ease of use - However I have stumbled upon a
problem that I cannot get around - PLEASE help me if you can...

I have a formula that returns a numeric value to a specific cell, Say A1,
this number will always be in the range of 0 to 1,000. A number of different
variables influence what the returned value is and this value changes
approximately 5 times per second. I want to be able to show the highest and
lowest value achieved in cells B1 (highest) and B2 (lowest) and have these
two cells both start out at 0 but auto update as a new high or low is
returned in A1

Any ideas on how I can achieve this or where I can go to find an answer
would be great.

Many Thanks,

Mike.