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

Share

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.

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.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

I will be presenting at two events in London in April 2020. Come & join me.

Chandoo is an awesome teacher
5/5

– Jason

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

## Related Tips

### 64 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. Jelle-Jeroen Lamkamp says:

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. Dick Kusleika says:

=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. Dick Kusleika says:

http://www.dailydoseofexcel.com/archives/2011/01/19/formula-challenge-3/

14. king wiemann says:

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

15. Pawel says:

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

16. Pawel says:

the text box cuts the formula

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

17. Nunes says:

=IF(
AND(ABS(A2-B2)

18. Nunes says:

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

20. Jon Sanderson says:

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?

21. diva says:

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

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

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

24. 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!

25. Gregory says:

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

=IF(ABS(A2-B2)

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

27. Fred says:

I came up with this:

=IF(AND(ABS(A1-B1)

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

29. Asheesh Shrivastav says:

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

30. Geoff says:

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

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

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

33. 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?

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

35. Ayush Jain says:

Hi Chandoo,

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

36. Ayush Jain says:

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

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

38. Eric says:

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

39. 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}

40. lhm says:

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

i believe this might be the shortest possible.

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

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

43. Fowmy says:

This array formula works for me:

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

44. Fowmy says:

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

45. Fowmy says:

I am struggling to post my full formula.

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

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

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

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

49. KIM WENNERBERG says:

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

50. ????? says:

hi
mercccccccccccccccccccccccccccccc

51. baran says:

merccccccccccccccccccccccccccccccccccc

52. Yogesh P says:

I wrote this one and worked fine....

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

53. Yogesh P says:

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

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

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

55. 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)))`

56. 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)))}

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

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

59. […] don’t know how to comment on Chandoo’s site, so I’ll post it […]

60. Ramchandra says:

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

• Ramchandra says:

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

61. Siva says:

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

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.