Excel maximum historical value

January 19, 2011 at 16:50:36
Specs: Windows Vista
Hello - I created a spreadsheet to track my stocks profit. The spreadsheet contains a cell (A1) which sums all the stocks' current profit. This cell changes (up or down) daily depending on the stock market.

How do I create a separate cell (B1) which shows the maximum historical profit ever found in A1? For example, if A1 starts at $1,000, then B1 should be $1,000. If A1 increases to $1,500, then B1 should also change to $1,500. But, if A1 drops below $1,500, then B1 should REMAIN at $1,500.

Thank you.
Mike


See More: Excel maximum historical value

Report •


#1
January 19, 2011 at 20:49:40
How does the value in A1 get updated?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 20, 2011 at 07:20:20
A1 is a formula which sums a set of numbers. The set of numbers are updated manually.

Report •

#3
January 20, 2011 at 12:20:46
You could use a Worksheet_Change macro that monitors A1 and only updates B1 when A1 exceeds it.

Let's say you are manually entering values in A2:A10. This code should do what you ask.

Modify the "A2:A10" range to match the range where you are making your manual entries.


Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
    If Range("A1") > Range("B1") Then Range("B1") = Range("A1")
  End If
End Sub

Right-Click the sheet tab for the sheet where you want this to happen and paste the code into the pane that opens.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 20, 2011 at 18:41:42
That makes sense. I'll give it a try. Thanks so much for your help.

Report •

Ask Question