Find the Average of Closest 2 Numbers out of 3 [formula challenge]
Today I am asking you a tricky formula question. This is asked by Ionel on the Introduction to VLOOKUP, OFFSET & MATCH Formulas post.
The question is,
I have data in three columns: A,B,C and I want to get the average of the closest two values out of three in each row. Could you help me with a formula for this?
Now, how would you go about it?
What does closest of two mean?
We can assume that closeness is nothing but distance between 2 numbers on numeric scale. So 3 is closer to 2 and 4 compared to 1 or 5.
Your challenge:
Assuming your data is in A2:C10, what formula will you write in D2:D10 to solve this?
Go ahead and get some coffee and get thinking.
Want to copout?
I have posted one solution in the next comment. You can see how I went about solving it.
More Formula Challenges:
 When does Thanksgiving occur on same date again?
 How to calculate total working hours between 2 dates?
 How many days are overlapped between 2 ranges of dates?
 Average of Top 5 Values
 … More Homework & Challenges in Excel
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.

Leave a Reply
« Excel Links – My First International Excel Workshop Edition  220 Excel Tips, Tutorials, Templates & Resources for You [Celebrating 20k RSS Members] » 
61 Responses to “Find the Average of Closest 2 Numbers out of 3 [formula challenge]”
Chandoo  liked your helper column approach. But I think this problem can have multiple answers in certain cases. E.g. 21,22,23 (from your file) the closest two can be 21,22 or 22,23 and hence the average can be 21.5 or 22.5!
Hi Chandoo, I quickly tried something for this. But I have used a few helper columns.
http://sheet.zoho.com/public/rameshs_zoho/averageofclosesttwo
Acknowledging the problem mentioned by Vipul, a simple formula suffices:
=if(max(a1:c1)median(a1:c1)> median(a1:c1)min(a1:c1), mean(median(a1:c1), min(a1:c1)), mean(median(a1:c1), max(a1:c1)))
Vipul's problem could be caught with an additional if statement, or you can play with > or >= to sort out these cases within the formula above.
In D2
=IF(COUNTIF(A2:C2,AVERAGE(A2:C2))=1,"", IF(COUNTIF(A2:C2,">"&AVERAGE(A2:C2))=1, (SUM(A2:C2)MAX(A2:C2))/2,(SUM(A2:C2)MIN(A2:C2))/2))
Whoops, guess that should be average() instead of mean().
Too much Python and too little Excel of late...
=(SUM(A1:C1)CHOOSE(COUNTIF(A1:C1,">"&AVERAGE(A1:C1)),MAX(A1:C1),MIN(A1:C1)))/2
In cell D2:
=IF(AND(ABS(A2B2)
=(SUM(A2:C2) CHOOSE(COUNTIF(A2:C2,">"& AVERAGE(A2:C2)), MAX(A2:C2), MIN(A2:C2)))/2
well my last comment got trimmed somehow (actually got =LEFT()'ed)
here it goes again
in cell D2 and with no helper column(s)
=IF(AND(ABS(A4B4)
=MIN(ABS(A3:B3B3:C3), ABS(A3C3)) (as an array formula, CtrlShift Enter)
=IF(ABS(LARGE(A1:C1,2)MAX(A1:C1))
[...] don’t know how to comment on Chandoo’s site, so I’ll post it here. I have data in three columns: A,B,C and I want to get the average of [...]
http://www.dailydoseofexcel.com/archives/2011/01/19/formulachallenge3/
=if((abs(C2)abs(B2))
{=AVERAGE(MIN(IF(ABS(MEDIAN($A2:C2)$A2:C2)>0,$A2:C2)),MEDIAN($A2:C2))}
the text box cuts the formula
{=AVERAGE(MIN(IF(ABS(MEDIAN($A2:C2)$A2:C2)>0,$A2:C2)),
MEDIAN($A2:C2))}
=IF(
AND(ABS(A2B2)
how can i post a comment without having it trimmed?!?!?
Hi Chandoo  =SUM(MAX(((SMALL(ABS(MEDIAN($A2:$C2)$A2:$C2),2)=(ABS(MEDIAN($A2:$C2)$A2:$C2)))*$A2:$C2)),MAX(((MIN(ABS(MEDIAN($A2:$C2)$A2:$C2))=(ABS(MEDIAN($A2:$C2)$A2:$C2)))*$A2:$C2)))/2 (CSE formula)
This works for me!
=(IF(IF(MIN(ABS(LARGE(A2:C2,2)  LARGE(A2:C2,1)), ABS(LARGE(A2:C2,2)  LARGE(A2:C2,3))) = ABS(LARGE(A2:C2,2)  LARGE(A2:C2,1)),1,2) = 1, LARGE(A2:C2,1), LARGE(A2:C2,3)) + LARGE(A2:C2,2)) / 2
I tried to edit this formula for a data set of 4 values (average the 3 closest values) but was unsuccessful. Any suggestions?
how about this one ?
=MIN(AVERAGE(A2,B2),AVERAGE(A2,C2),AVERAGE(B2,C2))
Hi,
Is this formula correct?
IF(ABS(A2B2)=MIN(ABS(A2B2),ABS(B2C2),ABS(C2A2)),AVERAGE(A2,B2),IF(ABS(B2C2)=MIN(ABS(A2B2),ABS(B2C2),ABS(C2A2)),AVERAGE(B2,C2),AVERAGE(C2,A2)))
Hopefully this comes through okay... there's a couple of lessthan signs in it.
Oldschool...no array formulas. Could quickly get ugly if you need to compare more than 3 columns.
=IF(ABS(A2B2)<MIN(ABS(A2C2),ABS(B2C2)),AVERAGE(A2,B2),IF(ABS(A2C2)<MIN(ABS(A2B2),ABS(B2C2)),AVERAGE(A2,C2),AVERAGE(B2,C2)))
Rob
=IF(ABS(MAX(A2:C2)AVERAGE(A2:C2))<= ABS(MIN(A2:C2)AVERAGE(A2:C2)), (SUM(A2:C2)MIN(A2:C2))/2, (SUM(A2:C2)MAX(A2:C2))/2)
I finally figured it out  escape the lessthan character!
You're giving me a headache Chandoo. Here's my formula:
=IF(ABS(A2B2)
Well, the formula didn't post even though I had everything in the comment box. Not worry, @Rob had the same formula, he beat me to the punch.
I came up with this:
=IF(AND(ABS(A1B1)
Nice puzzle. Here's my best attempt:
=(SUM(A2:C2)IF(AVERAGE(A2:C2)>MEDIAN(A2:C2),MAX(A2:C2),MIN(A2:C2)))/2
Hi Chandoo,
I have attempted using IF statement
=IF(AND(
(ABS(A3B3))
"=AVERAGE(MEDIAN(A1:C1),IF(MEDIAN(A1:C1)
=IF(MAX(A1:C1)MEDIAN(A1:C1)>MEDIAN(A1:C1)MIN(A1:C1),(MIN(A1:C1)*2+(MEDIAN(A1:C1)MIN(A1:C1)))/2,(MEDIAN(A1:C1)*2+(MAX(A1:C1)MEDIAN(A1:C1)))/2)
=IF(LARGE(A2:C2,1)LARGE(A2:C2,2)>LARGE(A2:C2,2) LARGE(A2:C2,3), (LARGE(A2:C2,2)+LARGE(A2:C2,3))/2, (LARGE(A2:C2,1)+LARGE(A2:C2,2))/2)
Some very nice and impressive formulae guys, but where in the "real world" would you want to calculate the average of the two closest items to the exclusion of the remaining items?
=IF((MAX(A2:C2)MEDIAN(A2:C2))>(MEDIAN(A2:C2)MIN(A2:C2)),AVERAGE(MIN(A2:C2),MEDIAN(A2:C2)),AVERAGE(MAX(A2:C2),MEDIAN(A2:C2)))
Hi Chandoo,
How about this ?
=(SUM(A1:C1)LARGE(A1:C1,1))/2
Please ignore my solution.... It was about the average of two smallest numbers ðŸ™‚
first, the middle number is always part of the average
second, the numbers are not ordered, so make sure no negative numbers pop up
finally, I prefer small(1,2,3) to min,median,max because it allows for more flexibility (m,m,m works only for three numbers, small can be extended to any number of columns)
So while the below is more complex than some  I think it has the benefit of more universal application: (array enter)
=INDEX(((SMALL(A2:C2,{1,1,2})/2+SMALL(A2:C2,{2,3,3})/2)),
MATCH(MIN(ABS(SMALL(A2:C2,{1,1,2})SMALL(A2:C2,{2,3,3}))),
ABS(SMALL(A2:C2,{1,1,2})SMALL(A2:C2,{2,3,3})),0))
{=SUM((A1:C1""(AVERAGE(A1:C1)STDEV(A1:C1)))*A1:C1)/2}
OK, trying again:
{=SUM((A1:C1 "less than or equal" (AVERAGE(A1:C1)+STDEV(A1:C1)))*(A1:C1 "greater than or equal" (AVERAGE(A1:C1)STDEV(A1:C1)))*A1:C1)/2}
=QUARTILE(A1:C1,1+2*(MEDIAN(A1:C1)>SUM(A1:C1)/3))
i believe this might be the shortest possible.
Fortunately, MEDIAN immediately gives you one of the values when you have an odd number of values. Finding the other value is a bit cumbersome.
AVERAGE(MEDIAN(A2:C2),ABS(MIN(ABS(MEDIAN(A2:C2)MAX(A2:C2)), ABS(MEDIAN(A2:C2)MIN(A2:C2)))MEDIAN(A2:C2)* IF(ABS(MEDIAN(A2:C2)MAX(A2:C2))> ABS(MEDIAN(A2:C2)MIN(A2:C2)),1,1)))
I think, the solution posted by Larry is very straightforward and easy for anyone to understand:
=IF(LARGE(A2:C2,1)LARGE(A2:C2,2)>LARGE(A2:C2,2) LARGE(A2:C2,3), (LARGE(A2:C2,2)+LARGE(A2:C2,3))/2, (LARGE(A2:C2,1)+LARGE(A2:C2,2))/2)
This array formula works for me:
{=SUM(((ABS($A1:$C1MEDIAN(A1:C1))
My previous post did not display the full formula, how about this one ?
{=SUM(((ABS($A1:$C1MEDIAN(A1:C1))
I am struggling to post my full formula.
=SUM(((ABS($A1:$C1MEDIAN(A1:C1))
Hi guys,
This is my first post in Chandoo though i have been a constant visitor since past 2 months. Must say this is ons of the best excel sites and i like the business writing skills of chandoo apart from the excel skills of course.
Ok, so here goes my formula is:
=IF(AND(ABS(B3C3)
=MAX(ABS(A1B1),ABS(A1C1),ABS(B1C1))/2
Notes:
Isn't it this simple??? All those IFs and
What are above comments about middle value always being used? I don't think that is the requirement.
In above post we assume source data is in col A:C.
THere are three potential pairs of numbers to compare, thus 3 arguments for MAX.
Whereas the final number is to be average of two numbers, I just divided MAX difference value by 2 instead of using AVERAGE.
Now I reread the requirement my formula should be MIN, not MAX
hi
mercccccccccccccccccccccccccccccc
merccccccccccccccccccccccccccccccccccc
I wrote this one and worked fine....
=IF(SMALL(A2:C2,2)SMALL(A2:C2,1)
why my formula is not displayed correctly in my last post?
j'ai lu les reponse mais pa deveritable reponse pour moi j'ai porcede comme suit: j'ai fai la soustraction entre tous les trois chiffres en suite un comparaison et je calacul la moyenne sauf la formule elle tres grande mais elle marche impecable si vous voulez je ous l'envoi par email nemyac100@yahoo.fr
Google translate:
I read the response but pa deveritable answer for me I porcede as follows: I fai subtraction between every three digits in a row and I compared the average calacul formula except it very much but it works if impecable I want you to send e mail nemyac100@yahoo.fr
Dead thread, but I still felt like sharing. Does not address the possibility of there being multiple couples with same range.
=IF(MAX(A2:C2)LARGE(A2:C2,2)<=SMALL(A2:C2,2)MIN(A2:C2),(MAX(A2:C2)+LARGE(A2:C2,2))/((COUNT(A2:C2)1)),(SMALL(A2:C2,2)+MIN(A2:C2))/((COUNT(A2:C2)1)))
{=AVERAGE(MEDIAN(A2:C2),INDEX(A2:C2,,MATCH(SMALL(ABS(MEDIAN(A2:C2)A2:C2),{2}),ABS(MEDIAN(A2:C2)A2:C2),0)))}
Hey! I guess the challenge is over now... sorry I just discover it. Most of your proposal doesn't work. here mine:)
SI(ABS(A3B3)=MIN(ABS(A3B3);ABS(A3C3);ABS(B3C3));MOYENNE(A3;B3);SI(ABS(A3C3)=MIN(ABS(A3B3);ABS(A3C3);ABS(B3C3));MOYENNE(A3;C3);MOYENNE(B3;C3)))
Note: I use Excel in french so just replace "Si" by "if" and "MOYENNE" by "AVERAGE".
not the most "elegant" but it works.
=IF(ABS(B2A2)<ABS(B2C2),AVERAGE(A2:B2),IF(A2=C2,AVERAGE(A2,C2),AVERAGE(B2:C2)))
[…] http://chandoo.org/wp/2011/01/19/averageofclosest2numbers/ […]
[…] don’t know how to comment on Chandoo’s site, so I’ll post it […]