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!!

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!!

Well, since nobody chimed in with a VBA method.....

Just be aware that there are a lot of things that can go wrong with this.

First, you have to make sure things are as error resistant as possible. You
should include an error check in the average formula that returns a blank if
there is an error.

OK....

J2 = average

Goto ToolsOptionsCalculation

Select Iteration and accept the default of 100.

Formula in J8 for max:

=IF(ISERROR(J2),"",MAX(J2,J8))

Formula in K8 for min:

=IF(ISERROR(J2),"",IF(J2="","",IF(K8=0,J2,MIN(J2,K 8))))

Big caveat - assume this works for a period of time and then one day
something happens to the average that causes an error. That will cause a
fatal error in both the Min and Max formulas. The previous values in those
cells will be gone with NO CHANCE of ever retrieving that data. That is the
main reason these types of operations are not recommended or widely used.

Have a look at this thread that is similar to what you want and consider
what Gord Dibben has to offer. You should be able to adapt his code to suit
your needs.

http://tinyurl.com/8p6ar

Biff

"Biff" wrote in message
...
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!!

THANKS BIFF!

I didn't get a chance to check out Dibben's post in the other thread but
thanks for the help. It seems to be working fine.

"Biff" wrote:

Well, since nobody chimed in with a VBA method.....

Just be aware that there are a lot of things that can go wrong with this.

Thanks for the feedback! Hope that works out for you!

Biff

"yongkalep" wrote in message
...
THANKS BIFF!

I didn't get a chance to check out Dibben's post in the other thread but
thanks for the help. It seems to be working fine.

"Biff" wrote:

Well, since nobody chimed in with a VBA method.....

Just be aware that there are a lot of things that can go wrong with this.