Challenge #111: Make a Weekly Challenge Dream Team!

[JoeM]

Last week's solution can be found here!

This week's challenge we are going to create a Weekly Challenge dream team... on a budget! Provided is a list of participants and the number of Challenges they have completed. But here's the rub, each person on your team is going to cost you some money and each ranking position has a 'salary' or 'value' associated with it. You first task is going to prep the data to attach a value to each person's rank. After assigning values, it's time to recruit your team of people. You goal when recruiting your team is to have a team of people that have completed the most weekly challenges. There are t20 simple rules when building your team: 1) You can only spend $1,500,000, 2) Your team must have 8 people. When you find your team, share your solution, tell us how many challenges your team completed and sound off who made it!

Since there are a lot of answers and only a few 'best answers', we wont be divulging our solution until next week. Good luck!

Note: The rank we used was a dense rank. So if my first two challengers tied, they both would be given a rank of one and the next (third) person would be given a rank of two. Some other methods would have given the third person the rank of three, but for the sake of the challenge we kept it 'dense'.

[patrick_digan]

I've got my team picked out!

Spoiler

I don't know my answer is the absolute best score possible, and my method isn't going to guarantee I get exactly 8 people. But it happened to get 8 in this case, and 681 seemed like a good number. Congrats to @jasperlch, @jamielaird, @SeanAdams, @samN, @LandonG, @nick_ceneviva, @CHarrison, @MaxGiegerich on making my team!

[jamielaird]

My invoice is in the mail @patrick_digan :D

[patrick_digan]

... Are substitutions\trades\re-drafts allowed?

Spoiler

I checked my answer with the optimization tool only to realize that my last pick was not optimal (my apologies to @MaxGiegerich, but @alex has taken his place on my team). My mistake was having my salary be < 1,500,000 instead of <= 1,500,000!! Both methods now yield the same results, and my challenges are up to 685. I've never used the optimization tool before, and the optimization fantasy sports lineup sample was very helpful. I'm hopeful that my team is now set.

[kat]

My team got 685 completed challenges!

Spoiler

I couldn't think of a way to guarantee 8 team members - I sort of just got lucky. Curious to see what the best way will be.

Congratulations @jasperlch @jamielaird @SeanAdams @samN @LandonG @nick_ceneviva @CHarrison and @alex for making the team!

[jamielaird]

Here's my solution.  I found this example workflow (as mentioned by @patrick_digan) a big help in understanding the Optimization tool.

Spoiler

Everything to the right of the optimization tool is pretty much just to generate a neat list of team members and validate our cost.  I noticed that the selection of my eighth team member was arbitrary based on the ordering of the list, so I used a RAND() function to make this selection random.

My team is: @alex, @CHarrison, @jamielaird, @jasperlch, @LandonG, @nick_ceneviva, @samN and @SeanAdams

Glad to see I made the cut for my own team! (Speaking of which - is everyone including themselves in their own teams? I didn't think of that until after I had a solution, and luckily I was already in there).

[patrick_digan]

@jamielaird I was initially excited about my high price tag... But then I realized I was over valued. I didn't make my own dream team, but every dream team needs a coach to lead them right?

[estherb47]

If anyone would like to hire me at this value, I'd love to join your dream team.

But, looks like I'm in good company as a coach, with @NicoleJohnson, @PhilipMannering, @ggruccio, @patrick_digan, @JosephSerpis, @LordNeilLord, and @Natasha

Spoiler

Used the Tile tool to assign the rankings for the salary match ups, using the Unique Values method on the Challenges field.

[ggruccio]

Hope to see my team at Inspire!!!

Spoiler

Used the optimization tool to draft @jamielaird, @SeanAdams, @samN, @jasperlch, @LandonG, @nick_ceneviva, @CHarrison, and @alex.  They have completed 685 challenges - an impressive average of 85.6 each!