Hi!

You could do this using an intentional circular reference but you'd be
better off using an event change macro. I can't help with the macro but if
nobody chimes in with something I'll be back to show you the circular
formula method.

Biff

"yongkalep" wrote in message
...
I'm not totally sure how to ask this but here goes...

I have a cell that uses a simple AVERAGE function. It keeps track of
evaluation ratings and gives me an overall average of all evaluations.
Naturally, it changes all the time as I input the evaluation numbers on
other
worksheets in the workbook.

I would like two other cells that refer to my overall average, one of
which
will display the highest average so far achieved, the other will display
the
lowest average so far.

Make sense? I want a formula that can compare a cell's current value with
another cell and replace the current value if the conditions are met.
Something like:

"If that value is greater than me, then replace me with that value."

I tried something like the following where J2 is the cell with my average
and J8 is where I want the "highest average yet"

=IF(J2J8,J2)

Of course, it is circular and won't work. I tried to set the number of
iterations to 1 or 2 but it still doesn't work all the time. Sometimes it
gives me the number, other times it says FALSE.

Are there functions that do this kind of thing?

THANKS!!