On Mon, 19 Sep 2005 23:32:36 GMT, "Sam via OfficeKB.com"
wrote:

Hi Ron,

Thank you for your time and help - the Formulas provide the required results -
great.

Cheers,
Sam

Ron Rosenfeld wrote:
Hi Ron,

Do you know of a workaround should the situation arise?

This **array** function will look for the Value that is *closest* to the
Average, and then match the corresponding label.

I have used NAME'd ranges for Labels and Values, but you can use cell
references if you prefer.

=INDEX(Labels,,MATCH(MIN(ABS(Values-AVERAGE(
Values))),ABS(Values-AVERAGE(Values)),0))

If you want to exclude 0's, then for the AVERAGE function substitute
SUM(Values)/COUNTIF(Values,"<0")

So you wind up with:

=INDEX(Labels,,MATCH(MIN(ABS(Values-SUM(Values)/
COUNTIF(Values,"<0"))),ABS(Values-SUM(
Values)/COUNTIF(Values,"<0")),0))

Remember, with an array formula you must hold down <ctrl<shift while hitting
<enter. Excel will place braces {...} around the formula.

I did not bother to ROUND the AVERAGE, but you could if you need to.

--ron


You're welcome. Glad it works for you.


--ron