tracking highest value

dijon03

New Member
Joined
Aug 10, 2012
Messages
4
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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jeffmb

Board Regular
Joined
Jul 18, 2008
Messages
184
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.

Code:
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Aug 10, 2012
Messages
4
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

New Member
Joined
Aug 10, 2012
Messages
4

ADVERTISEMENT

Excel 2010
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Board Regular
Joined
Jul 18, 2008
Messages
184

ADVERTISEMENT

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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Aug 10, 2012
Messages
4
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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! :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,366
Messages
5,601,208
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top