# 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

#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 •