Hi All,

a fellow forum member provided me with the following formula to identify the score closest to the average from a list of scores:

=INDEX(J2:J44,MATCH(MIN(ABS(J2:J44-J45)),ABS(J2:J44-J45),0))

Where I have 2 score equidistant from the average, 1 above and 1 below, this formula seems to prefer the lower score.......

That's not a problem, however, how can I alter this formula to choose the closest number over the average??

I.e. I have a list of scores running down column J, I use a basic formula to work out the average and put the number in cell J45. I have the formula above in cell J46 and I would like to run a similar formula in cell J47 giving me :-

A) The average in cell J45
B)The score closest to the average (below) in cell J46
C) The score closest to the average (above) in cell J47

Thanks in advance.

Simon.