Excel - AVERAGE Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have 36 persons with values between 180 and 420. I need to pair them up 2 and 2 to get the combined value closest to average
Solved by G. S. in 28 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 02/05/2018 - 11:42
Hello, I understand that you want to pair your 36 persons depending on their values, right?
User 02/05/2018 - 11:42
Excelchat Expert 02/05/2018 - 11:43
Can you show me a sample data using the document preview to the right please?
Excelchat Expert 02/05/2018 - 11:44
You mentioned that the maximum value would be 420?
User 02/05/2018 - 11:44
And then make excel choose the best match to get closest value to the average
User 02/05/2018 - 11:44
Just example
Excelchat Expert 02/05/2018 - 11:44
In this case, what is the expected output?
User 02/05/2018 - 11:45
help me out
User 02/05/2018 - 11:45
Have to find the average then the best match
Excelchat Expert 02/05/2018 - 11:45
I will, once I understand your requirement.
Excelchat Expert 02/05/2018 - 11:46
In the example you provided, what is the expected output?
Excelchat Expert 02/05/2018 - 11:46
You don't have to write a formula, just manually solve it.
User 02/05/2018 - 11:46
I don't understand
Excelchat Expert 02/05/2018 - 11:47
Okay, you provided the data. What do you need help with exactly? What is the expected answer using the sample you provided.
Excelchat Expert 02/05/2018 - 11:47
If you could provide the logic then we can translate it into Excel formulas.
User 02/05/2018 - 11:47
Excelchat Expert 02/05/2018 - 11:48
In the example, the average is 310. What do you need to do with 310 then?
User 02/05/2018 - 11:49
I need excel to match the 4 people together in the best way so they are all close to average combined
User 02/05/2018 - 11:49
English isn't my main language so it's not easy
Excelchat Expert 02/05/2018 - 11:49
So if you are going to manually combine them which will be combined with who?
User 02/05/2018 - 11:50
Johnny + Darlene and Sam + Kenneth
Excelchat Expert 02/05/2018 - 11:50
Thank you. Once we find out who will be paired, what do you want to happen?
User 02/05/2018 - 11:51
But that is what i want. Since i have 36 people i need to know the best partner for each
Excelchat Expert 02/05/2018 - 11:55
The formula in columns F and G will do this for you but it will only work based on the sample data your provided. If you have more, then we will need to adjust the formula.
Excelchat Expert 02/05/2018 - 11:55
The range will need to be adjusted if you have more.
Excelchat Expert 02/05/2018 - 11:57
For instance, the formula in H and I have been adjusted to accomodate the extra names.
Excelchat Expert 02/05/2018 - 11:57
So basically, just adjust the range $B$3:$B$6 and $C$3:$C$6 to whatever range you have
User 02/05/2018 - 11:59
I'm a bit lost
Excelchat Expert 02/05/2018 - 11:59
Basically the formula grabs the highest value and the lowest value and then pairs them.
Excelchat Expert 02/05/2018 - 12:00
Columns J and K will show that.
Excelchat Expert 02/05/2018 - 12:00
Then the rest of the formula will just be to assign the names.
Excelchat Expert 02/05/2018 - 12:01
If you are a beginner in Excel I'm afraid there really is no easy to explain this. So I won't feel bad if you find it a bit difficult.
User 02/05/2018 - 12:02
I've never tried excel so i have 30 minutes in it.
Excelchat Expert 02/05/2018 - 12:03
If that's the case then I will have to be very honest with you, there is absolutely no way for me to explain this to you within the time limit. And I don't mean that to be insulting, it's just that your requirement requires pretty advanced solutions, and the solutions took me years before I could understand them.
Excelchat Expert 02/05/2018 - 12:04
If you could provide me your actual file then I can see if I can apply the formula in your file and then just send it back to you.
Excelchat Expert 02/05/2018 - 12:04
But I can't promise you that I'll be able to explain how it works.
Excelchat Expert 02/05/2018 - 12:04
Do you have an Excel file you can share?
User 02/05/2018 - 12:05
It's okay. I can't share the files Could you maybe tell me what formula you used
Excelchat Expert 02/05/2018 - 12:05
The formulas are all in the sheet to the right, do you see them?
Excelchat Expert 02/05/2018 - 12:05
Here's for the first name:
Excelchat Expert 02/05/2018 - 12:05
Excelchat Expert 02/05/2018 - 12:05
Here's for its pair:
Excelchat Expert 02/05/2018 - 12:05
User 02/05/2018 - 12:06
Okay so the formula names are index and match right?
Excelchat Expert 02/05/2018 - 12:06
I'd also recommend downloading the file to the right.
Excelchat Expert 02/05/2018 - 12:06
The Index and Match are just to grab the names.
Excelchat Expert 02/05/2018 - 12:06
The small and large are the actual engines of the formula.
User 02/05/2018 - 12:07
You are right. It's a bit complicated
Excelchat Expert 02/05/2018 - 12:07
I'm so sorry about that. This is the simplest solution I can think of.
Excelchat Expert 02/05/2018 - 12:08
And I don't blame you at all, I spent years working with Excel before I could understand this. I won't be surprised if 30 minutes won't be enough for you to understand it.
User 02/05/2018 - 12:08
I'll try my best to understand the work you've done for me. Thank you for your help.
Excelchat Expert 02/05/2018 - 12:09
You are welcome. I'd recommend starting to learn how small() and large() works so you'll have an idea how the numbers are paired.
Excelchat Expert 02/05/2018 - 12:09
Would there be anything else that I can help you with regards to the original question?
User 02/05/2018 - 12:09
No thank you. This was good enough
Excelchat Expert 02/05/2018 - 12:10
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert 02/05/2018 - 12:10
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert 02/05/2018 - 12:10
Thank you for contacting Got It Pro. Have an awesome day!
User 02/05/2018 - 12:10
You too!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc