Formula help - Ranking each value in a range by closeness to Mean.

[peterbiddle6256]

Hi, I need some help with a formula.

I have a range of values in cells I4:I227

In column J4:J227, I need a formula that will rank each value in I4:I227 by it's closeness to the Mean value. I need those values ranked in descending order.

For example if the Mean of I4:I227 is 1.21, and the value in cell I185 is 1.211 (which is closest value in the range to the mean), then J185 would show a rank of "223" because I185 is the closest to the Mean of all 223 values in descending order.

Hope that makes sense. Any help would be appreciated!

Thanks.

[#2]

@peterbiddle6256 

= LET(
    mean,      AVERAGE(values),
    deviation, ABS(values - mean),
    SORTBY(values, deviation, -1)
  )

This sort of calculation is natural within the context of Excel 365 array thinking, but laboured in legacy systems.