tracking highest value

[dijon03]

I have a formula in one cell that determines a value. As the value changes, I want another cell to record the highest value of the original cell. So as the first cell grows in value, the new cell shows the highest value, but if the original cell goes down in value, the new cell does not change.

 

[jeffmb]

Copy and paste the following code into the appropriate Sheet folder in your Visual Basic editor. This example changes the value of cell B2 whenever the value of A2 is increased to a value greater than the value already in B2. Modify the cell references as required.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then
        If Range("A2").Value > Range("B2").Value Then
            Range("B2").Value = Range("A2").Value
        End If
    End If
End

Sub

 

[T. Valko]

I have a formula in one cell that determines a value. As the value changes, I want another cell to record the highest value of the original cell. So as the first cell grows in value, the new cell shows the highest value, but if the original cell goes down in value, the new cell does not change.

One way is to use a formula which uses an intentional circular reference.

What version of Excel are you using?

 

[dijon03]

Jeffmb, seriously? My Visual Basic Editor? At this point, you should be completely laughing at me. I was have no idea how to get the visual basic editor and then once I get there, do I actually just cut and paste your code at the bottom? I see it's an "IF" formula, but I could not figure out how to make that work directly in Excel. How do I get to the visual basic editor? Remember, I'm a dumb old man!

 

[dijon03]

Excel 2010

 

[T. Valko]

Excel 2010

Ok, I don't have Excel 2010 so the location of this setting might be different.

Goto File>Excel Options>Formulas>Calculation Options

Select: Enable iterative calculation

OK

Let's assume cell A2 contains your formula.

Enter this formula in B2:

=MAX(A2,B2)

If you ever want to reset the value in cell B2 you can either re-enter the formula or select the cell, double click then hit Enter, or, select the cell, hit function key F2, then hit Enter.

 

[jeffmb]

Set up the Developer tab by:
Go to Excel Options (Microsoft Orb)/select Popular (on left panel)/check 'Show Deleloper tab in the Ribbon'/Click OK
Then click Visual Basic on the ribbon of the Developer tab. The editor should open. There you will see the object browser on the left panel. If not, press ctrl+r. You will see the open excel files and their sheet names listed. Double click the appropriate sheet, then copy and paste the code into the right panel.

 

[T. Valko]

Set up the Developer tab by:
Go to Excel Options (Microsoft Orb)/select Popular (on left panel)/check 'Show Deleloper tab in the Ribbon'/Click OK
Then click Visual Basic on the ribbon of the Developer tab. The editor should open. There you will see the object browser on the left panel. If not, press ctrl+r. You will see the open excel files and their sheet names listed. Double click the appropriate sheet, then copy and paste the code into the right panel.

I think they replaced the "Excel Orb" with a File menu in Excel 2010?

That's why I'm unsure of the locations for the menu commands in my suggestion.

 

[dijon03]

T. Valko, that worked. It was a little quirky to copy the formula down the page, you actually had to go into each sell and just hit enter. Weird! But it worked! Thanks.

 

[T. Valko]

T. Valko, that worked. It was a little quirky to copy the formula down the page, you actually had to go into each sell and just hit enter. Weird! But it worked! Thanks.

Good deal. Thanks for the feedback!