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
Yes
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
Okay
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
=index($B$3:$B$10,match(large($C$3:$C$10,rows($F$2:F3)-1),$C$3:$C$10,0))
Excelchat Expert
02/05/2018 - 12:05
Here's for its pair:
Excelchat Expert
02/05/2018 - 12:05
=index($B$3:$B$10,match(SMALL($C$3:$C$10,rows($F$2:F3)-1),$C$3:$C$10,0))
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.
