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 close-ness 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 cop-out?
I have posted one solution in the next comment. You can see how I went about solving it.
64 Responses
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/average-of-closest-two
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(A2-B2)
=(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(A4-B4)
=MIN(ABS(A3:B3-B3:C3), ABS(A3-C3)) (as an array formula, Ctrl-Shift Enter)
=IF(ABS(LARGE(A1:C1,2)-MAX(A1:C1))
http://www.dailydoseofexcel.com/archives/2011/01/19/formula-challenge-3/
=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(A2-B2)
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(A2-B2)=MIN(ABS(A2-B2),ABS(B2-C2),ABS(C2-A2)),AVERAGE(A2,B2),IF(ABS(B2-C2)=MIN(ABS(A2-B2),ABS(B2-C2),ABS(C2-A2)),AVERAGE(B2,C2),AVERAGE(C2,A2)))
Hopefully this comes through okay… there’s a couple of less-than signs in it.
Old-school…no array formulas. Could quickly get ugly if you need to compare more than 3 columns.
=IF(ABS(A2-B2)<MIN(ABS(A2-C2),ABS(B2-C2)),AVERAGE(A2,B2),IF(ABS(A2-C2)<MIN(ABS(A2-B2),ABS(B2-C2)),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 less-than character!
You’re giving me a headache Chandoo. Here’s my formula:
=IF(ABS(A2-B2)
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(A1-B1)
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(A3-B3))
“=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:$C1-MEDIAN(A1:C1))
My previous post did not display the full formula, how about this one ?
{=SUM(((ABS($A1:$C1-MEDIAN(A1:C1))
I am struggling to post my full formula.
=SUM(((ABS($A1:$C1-MEDIAN(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(B3-C3)
=MAX(ABS(A1-B1),ABS(A1-C1),ABS(B1-C1))/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 re-read 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(A3-B3)=MIN(ABS(A3-B3);ABS(A3-C3);ABS(B3-C3));MOYENNE(A3;B3);SI(ABS(A3-C3)=MIN(ABS(A3-B3);ABS(A3-C3);ABS(B3-C3));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(B2-A2)<ABS(B2-C2),AVERAGE(A2:B2),IF(A2=C2,AVERAGE(A2,C2),AVERAGE(B2:C2)))
A different approach
“=IF(MAX(A2:C2)-ABS(G2)>ABS(G2)-MIN(A2:C2),AVERAGEIF(A2:C2,””&MAX(A2:C2)),AVERAGEIF(A2:C2,””&MIN(A2:C2)))”
Regret posting without G2
G2 = Average(A2:C2)
“=IF(MAX(A2:C2)-ABS(AVERAGE(A2:C2))>ABS(AVERAGE(A2:C2))-MIN(A2:C2),AVERAGEIF(A2:C2,””&MAX(A2:C2)),AVERAGEIF(A2:C2,””&MIN(A2:C2)))”
In the cell D2, (assuming the data is in A2:C2), the formula can be as follows:
=0.5*(SUM(A2:C2)-IF(AVERAGE(A2:C2)>AVERAGE(MAX(A2:C2),MIN(A2:C2)),MIN(A2:C2),MAX(A2:C2)))