How to capture a max value in a dynamic cell

Hi all, I've found similar topics but nothing that was specific to my needs. If there is another thread, please point the way.

I have a non-VBA sheet with many complex formulas that more or less do mathematical equations related to cryptography. There is a particular cell that calculates the Index of Coincidence (IC) of a string of text and I want to be able to run the sheet through many dynamic changes that affect that I.C. and capture the maximum value of that cell and save it into another cell to be updated if there is a new max.

I'm open to hearing about VBA or macros, but I'm trying to avoid using them, not that I am unfamiliar, I just don't want to use any code.

You don't really need to know the formula, its any formula that changes a value in a cell and you want a general solution to capturing the dynamic max value.

There should also be a way to reset the captured value to start over as needed.

Think of the question like those old gauges in cars that have a dynamic needle and another max needle that moved up with the dynamic needle but not down and therefore captured the max RPMs say.

I've found the concept of Iterative and timestamping captured values, but I really don't need a timestamp, and I don't need to capture every value or when it changes. The iterative function would watch a cell and capture the value to a helper cell when the dynamic cell value was greater than the helper cell stored value.

Suggestion deleted at the request of the OP.

If you are avoiding VBA, are you also avoiding circular references with iteration enabled? I have suggested using circular references for this sort of thing in the past:

Looking for minimum rather than maximum, but the idea is the same: https://www.excelforum.com/excel-for...amic-cell.html
looking for the maximum, links to prior threads, too: https://www.excelforum.com/excel-gen...ging-cell.html

Would that kind of approach work for you?

I know for a fact that if all I wanted was to capture the cell value when it changed, I would not need to look at the precedents to that cell.

Not so. The Worksheet Change Event handler does NOT fire for a change in the value generated by a formula. Easy enough to test. It only fires when a cell value (not formula) is changed either manually, or by code.

But, whatever, I'm happy to delete my response and duck out.

Wow, Mr Shorty, that was so simple. I knew about turning on iteratives in the options, but thought I would need a lot more than a simple MAX function for this to work.

All I used was =MAX(A1,B1) in the A1 cell; where B1 is the dynamic cell and A1 is the max storage cell. Perfect. Thank you. Saved me hours of trying to make the more complex timestamp version of this work for my simple use case.

The only thing it is missing is a way to reset the iterative cell, but I found that if I change A1 to =MAX(A1,A1), it resets to 0, and then when I change it back to MAX(A1,B1) it resets the max to the current value of the B1 cell. Good enough for my purposes.

EXTRA CREDIT: I read more of your posts. I used your reset idea to add another helper cell next to my max storage cell. Then changed the A1 formula to =IF(A2=1,0,MAX(A1,B1)), so anytime I want to reset the max and drop it down to the current dynamic value, I enter a 1 in the helper cell and then change it back to a 0 as a placeholder. Not that pretty but will suffice until I decide to get fancier with a button.

Thread marked as SOLVED.