# How to find a nearest number 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
``````

``````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?

EDIT:

Sorry, I did a poor job of reading your question, and just now realized that you clearly said you want to find the nearest `Weight` value to the average among the values for the city that the average was calculated for. So I've updated the answer below.

It looks like you found XOR LX's answer to a similar question, and you're pretty close to having it right.

XOR LX used a really neat little formula that gets around the limitations of `MATCH()` when searching through unordered data. I'll explain how it works below.

In the data table shown below, I've calculated the averages with:

`=AVERAGEIF(A\$2:A\$11,A14,B\$2:B\$11)` (I get different answers than what you have shown above).

and the nearest `Weight` to the average with:

`=INDEX((A\$2:A\$11=A14)*(B\$2:B\$11),MATCH(TRUE,(A\$2:A\$11=A14)*ABS(B\$2:B\$11-B14)=MIN(IF(A\$2:A\$11=A14,ABS(B\$2:B\$11-B14))),0))`

Note that this is an array formula, so it must be entered with CTRLShiftEnter, rather than just Enter. ______________________________________________________________________________

How it works:

`ABS(B\$2:B\$11-B14)` is an array of the differences between the average and all the numbers in the `Weight` list. And `(A\$2:A\$11=A14)` is an array of `True/False` values with `True` wherever the `City` equals `A14`. Multiplying these two together gives an array of those differences in the positions corresponding to `City = A14`, with `0` everywhere else.

Next we want to find the minimum of those differences, but we have to create a slightly different array, because `MIN()` will return `0` if any `0's` are in the array.

`IF(A\$2:A\$11=A14,ABS(B\$2:B\$11-B14))` checks where `City = A14`, and returns the differences between `Weight` and the average for those positions, with `False` everywhere else.

Taking the minimum of that array, `MIN(IF(A\$2:A\$11=A14,ABS(B\$2:B\$11-B14)))` gives the smallest difference for only those positions where `City = A14`.

Now the equality `(A\$2:A\$11=A14)*ABS(B\$2:B\$11-B14)=MIN(IF(A\$2:A\$11=A14,ABS(B\$2:B\$11-B14)))` gives an array of `True/False` values with `True` in the position of the smallest difference for the current `City`. `MATCH()` finds the position of the `True`, (which is the position of the nearest number) and that is fed to an `INDEX()` to return the actual value.

I hope this helps, and good luck.