How to keep a high water mark

**TerryM** · 11-10-2006, 11:52 AM

I would like to keep track of the largest value a certain cell ever reaches.
The monitored cell is a stock price.
The high water mark (HWM) value should be kept in a different cell.

Can anyone think of a way to do this using a formula?

Thanks,
Terry

**VBA Noob** · 11-10-2006, 12:34 PM

Hi,

If the numbers are input into the same row range every day..........

Set up an intentional circular reference:
Assume the range of numbers is A1:E1.
New numbers are entered each day.
Assume the formula cell is G1.

Goto Tools>Options>Calculation
Check Iteration ,OK

Formula in G1:

=MAX(A1:E1,G1)

In order to avoid the circular reference warnings and popups you must first
set iteration before you create the formula.

A disadvantage to doing this is that there is no audit trail, and, if for example you enter 1000 but meant to enter 100, ooops!

VBA Noob

**TerryM** · 11-10-2006, 03:01 PM

That works perfectly for me!!

Thanks for the explanation about iteration.

My Excel 2003 version does not have an OK, but it does have an iteration count. The default maximum count is 100. Is that an acceptable value?

Regards,
Terry

**VBA Noob** · 11-10-2006, 03:06 PM

Hi,

Glad I could help. Not sure about the default maximum count is 100 bit as I don't have excel 2003.

Is there anything on excel help ??

VBA Noob

How to keep a high water mark

LinkBack

Thread Tools

Rate This Thread

Display

How to keep a high water mark

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions