Forum Discussion

peterbiddle6256's avatar
peterbiddle6256
Copper Contributor
Aug 21, 2024

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

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.

excel
Formulas and Functions

1 Reply

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 21, 2024

    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.