Newbe Question ON Min, Max

J

Jim

I have a calculated value in cell A1 which changes daily. In cells B1, B2 I
need to place the upper and lower (High & Low) values that appear in A1 over
time but I'm not sure how to do this.

Suggestions greatly appreciated.
 
H

Harald Staff

This is absolutely not how Excel works. So you need a macro solution.
Righeclick the sheet tab, choose "view code", paste this in:

Private Sub Worksheet_Calculate()
If Me.Range("A1").Value > Me.Range("B1").Value Then
Me.Range("B1").Value = Me.Range("A1").Value
Me.Range("C1").Value = Now
End If
If Me.Range("A1").Value < Me.Range("B2").Value Then
Me.Range("B2").Value = Me.Range("A1").Value
Me.Range("C2").Value = Now
End If
End Sub

HTH. Best wishes Harald
 
J

Jim

Many thanks Harald, your macro seems to work just fine.

I must brush up on macros, it's been quite some time since I used them.
Right now I'm just able to step through your code using the F8 key, must
learn how to run code automatically.

Again, many thanks for your help.

Jim
 
D

Dave Peterson

This is a worksheet event (did you follow Harald's instructions to install
it???). It'll run each time excel recalculates. You don't need to run it
yourself.
 
J

Jim

Harald,

Just install your macro in my main worksheet and it works just great.

Many thanks.

Jim
 
Top