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:


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,


Please let me know what is wrong with my formula?

thanks in advance



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:


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

enter image description here ______________________________________________________________________________

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.