Lok up Formula
 

Hi,
I have the following data that I need a formula written for. Any help would be appreciated. Thanks.


In A6 through A17 I have people's names
In S6 through S17 I have a value for each person
In cell S30 I have the average of these values

Ultimately I would like to have a conditional formula to highlight in yellow the average of the range S6 though S17. I was able to highlight green the top and red the bottom through conditional formatting. I'm not sure how to get the average. To make it more difficult, the average # won't always equal what the average # actually is. Therefore, if getting a conditional formula to highlight the closest to the average number isn't possible, then I would like a formula in cell S27 to look up the names in A6 through A17 to look up each persons value in S6 through S17 and give me whoever is the closest to the average #.