Find the Average of Closest 2 Numbers out of 3 [formula challenge]
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.
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
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.
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] »|