Find the Average of Closest 2 Numbers out of 3 [formula challenge]

Posted on January 19th, 2011 in Excel Howtos - 59 comments

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?

Calculating Average of closest 2 numbers using ExcelWhat 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.

More Formula Challenges:

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

59 Responses to “Find the Average of Closest 2 Numbers out of 3 [formula challenge]”

  1. Vipul says:

    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!

  2. Ramesh says:

    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

  3. Daan says:

    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.

  4. 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))

  5. Daan says:

    Whoops, guess that should be average() instead of mean().
    Too much Python and too little Excel of late...

  6. Luke M says:

    =(SUM(A1:C1)-CHOOSE(COUNTIF(A1:C1,">"&AVERAGE(A1:C1)),MAX(A1:C1),MIN(A1:C1)))/2

  7. Nunes says:

    In cell D2:

    =IF(AND(ABS(A2-B2)

  8. Luke M says:

    =(SUM(A2:C2)- CHOOSE(COUNTIF(A2:C2,">"& AVERAGE(A2:C2)), MAX(A2:C2), MIN(A2:C2)))/2

  9. Nunes says:

    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)

  10. Brendan says:

    =MIN(ABS(A3:B3-B3:C3), ABS(A3-C3)) (as an array formula, Ctrl-Shift Enter)

  11. =IF(ABS(LARGE(A1:C1,2)-MAX(A1:C1))

  12. [...] 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 [...]

  13. king wiemann says:

    =if((abs(C2)-abs(B2))

  14. Pawel says:

    {=AVERAGE(MIN(IF(ABS(MEDIAN($A2:C2)-$A2:C2)>0,$A2:C2)),MEDIAN($A2:C2))}

  15. Pawel says:

    the text box cuts the formula

    {=AVERAGE(MIN(IF(ABS(MEDIAN($A2:C2)-$A2:C2)>0,$A2:C2)),
    MEDIAN($A2:C2))}

  16. Nunes says:

    =IF(
    AND(ABS(A2-B2)

  17. Nunes says:

    how can i post a comment without having it trimmed?!?!?

  18. Oli says:

    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)

  19. 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

    • Rachel says:

      I tried to edit this formula for a data set of 4 values (average the 3 closest values) but was unsuccessful. Any suggestions?

  20. diva says:

    how about this one ?

    =MIN(AVERAGE(A2,B2),AVERAGE(A2,C2),AVERAGE(B2,C2))

  21. arun says:

    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)))

  22. Rob says:

    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

  23. Justin says:

    =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!

  24. Gregory says:

    You're giving me a headache Chandoo. Here's my formula:

    =IF(ABS(A2-B2)

  25. Gregory says:

    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.

  26. Fred says:

    I came up with this:

    =IF(AND(ABS(A1-B1)

  27. Doug Jenkins says:

    Nice puzzle. Here's my best attempt:

    =(SUM(A2:C2)-IF(AVERAGE(A2:C2)>MEDIAN(A2:C2),MAX(A2:C2),MIN(A2:C2)))/2

  28. Asheesh Shrivastav says:

    Hi Chandoo,
    I have attempted using IF statement
    =IF(AND(
    (ABS(A3-B3))

  29. Geoff says:

    "=AVERAGE(MEDIAN(A1:C1),IF(MEDIAN(A1:C1)

  30. Yair says:

    =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)

  31. Larry says:

    =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)

  32. Dave says:

    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?

  33. Drazen says:

    =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)))

  34. Ayush Jain says:

    Hi Chandoo,

    How about this ?

    =(SUM(A1:C1)-LARGE(A1:C1,1))/2

  35. Ayush Jain says:

    Please ignore my solution.... It was about the average of two smallest numbers 🙂

  36. ikkeman says:

    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))

  37. Eric says:

    {=SUM((A1:C1""(AVERAGE(A1:C1)-STDEV(A1:C1)))*A1:C1)/2}

  38. Eric says:

    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}

  39. lhm says:

    =QUARTILE(A1:C1,1+2*(MEDIAN(A1:C1)>SUM(A1:C1)/3))

    i believe this might be the shortest possible.

  40. Ulrik says:

    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)))

  41. Fowmy says:

    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)

  42. Fowmy says:

    This array formula works for me:

    {=SUM(((ABS($A1:$C1-MEDIAN(A1:C1))

  43. Fowmy says:

    My previous post did not display the full formula, how about this one ?
    {=SUM(((ABS($A1:$C1-MEDIAN(A1:C1))

  44. Fowmy says:

    I am struggling to post my full formula.

    =SUM(((ABS($A1:$C1-MEDIAN(A1:C1))

  45. Abhishek says:

    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)

  46. KIM WENNERBERG says:

    =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.

  47. KIM WENNERBERG says:

    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.

  48. KIM WENNERBERG says:

    Now I re-read the requirement-- my formula should be MIN, not MAX

  49. ????? says:

    hi
    mercccccccccccccccccccccccccccccc

  50. baran says:

    merccccccccccccccccccccccccccccccccccc

  51. Yogesh P says:

    I wrote this one and worked fine....

    =IF(SMALL(A2:C2,2)-SMALL(A2:C2,1)

  52. Yogesh P says:

    why my formula is not displayed correctly in my last post?

  53. yacine says:

    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

  54. Lane says:

    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)))

  55. Ulrik says:

    {=AVERAGE(MEDIAN(A2:C2),INDEX(A2:C2,,MATCH(SMALL(ABS(MEDIAN(A2:C2)-A2:C2),{2}),ABS(MEDIAN(A2:C2)-A2:C2),0)))}

  56. Ivan says:

    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".

  57. PINOY-exceler says:

    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)))

Leave a Reply