Nearest Value to Average

I have a sample like this where I want to find nearest value to average

city and weight are two separate columns

city  weight
A      23
A      22
A      45
A      97
B      34
B      22
B      23
C      76
C      23
C      23

I made a pivot and calculated the average of weight for A- which is 46.75

I need to find nearest number for A which will be 45 in this case

I think i need to use index and match, but how would i do it if have 17,000 rows with duplicate city names and different weight values?

Any help I would appreciate

so answer i am looking for is

Row Labels  Average of WEIGHT     nearest number
A                 46.75            45
B                 38.75            34
C                  23              23

Most similar answers are not using this set, please help me set up this formula i have tried:

INDEX(rawdata,MATCH(MIN(ABS(weight-$B2)),ABS(weight-$B2),0),2)

But it look at at whole array of weight from A-C. I oonly want it to look at values for A when it is comparing average of A,

And then weight of B when comparing average of B,

AND SO ON….

Please let me know what is wrong with my formula?

thanks in advance

By: Birinder

One thought on “Nearest Value to Average

  1. Birinder

    here is the answer: =INDEX(rawdata,MATCH(SMALL(IF(combi=$A2,ABS(weight-$B2)),1),IF(combi=$A2,ABS(weight-$B2)),0),2)

Leave a Reply

Your email address will not be published. Required fields are marked *