Overview
You are consulting with a team of 30 students and professors at Brigham Young University who have created an online forum for students and professionals to perfect their data analytics skills. The team is excited about this project, but there’s a problem—they can’t decide on a name for the site. The team trusts your judgement, so they’ve brought you on to help them make a decision.
The team is divided between a list of 18 potential names. Each team member has completed a series of short online surveys that helped them rate each name on a scale of 1 to 100, but still, no clear winner stands out. Each survey response is kept in a log that you will find under the “Voting_Data” tab.
Many team members are passionate about this, so tensions are running high. Your task is to put all of the scores into a matrix that will identify which name is the real winner. Follow the instructions for each tab below to help you—hopefully, you will reveal the best name for the site. Good luck!
Instructions
Voting_Data:
Isolate the “User_Name”, “Website_Name”, and “Score Given” from the “Entry_Result” column. Fill these values into their respective columns.
Suggestions and Hints
I recommend you using either the TEXTSPLIT formula or Excel’s built-in Text-to-Columns feature to make quick work of this task. However, you can also use string formulas such as LEFT, RIGHT, MID, and FIND.
Identify each user’s “User_ID” and each potential site name’s “Name_ID”. These can be found in the “User_Data” and “Name_Data” tabs.
Suggestions and Hints
Excel’s XLOOKUP function can help with this. Don’t forget to use absolute references for the lists of usernames and site names.
Concatenate “User_ID” with “Name_ID” to create a unique ID for each entry that can identify both who cast each vote and what name they voted on.
Suggestions and Hints
Excel’s CONCAT function can help you with this.
Copy all of the data you have, and paste it in the correct columns as values (i.e., eliminating all formulas). Make sure that numbers are formatted as numbers.
Suggestions and Hints
Numbers formatted as text show a green error box in the top left of their cell. You can format these as numbers by highlighting them all and clicking on the error icon that appears.
User_Data:
Sum the total number of seconds each user spent voting on names (see “Seconds_Elapsed” on the “Voting_Data” sheet)
Suggestions and Hints
Excel has a great function called SUMIF. Make sure you understand what each part of the formula does. You should only need to use the formula once per user.
Remember again to use absolute references for anything you want to stay static as you drag the formula down.
Copy your formulas in the Total_Active_Seconds column, and paste them as values. Double check that they are formatted as numbers.
Sort users on the basis of “Total_Active_Seconds”, with the user who spent the most time positioned at the top. The users who spent the most time probably thought the hardest about it, so they should be first.
Name_Data
Sort each name based on its “Name_ID”, with the first name positioned at the top. This will help us order the matrix correctly so that we can see the patterns that emerge.
Name_Matrix
This is where it gets fun. Using the User ID’s and Name ID’s positioned around the empty matrix, fill the matrix with each score given by each team member to each name
Suggestions and Hints
Try using a mixture of CONCAT and XLOOKUP
Make sure you use absolute references for columns and rows where appropriate. If you are dragging a formula across a row, make sure just the row reference is absolute (e.g., “A$1”). If you’re dragging across a column, just the column reference is absolute (e.g., “$A1”).
Apply conditional formatting to your matrix to identify a clear trend in the winning name for our website.
Suggestions and Hints
The Color Scales options will give the best results
If you don’t identify a pattern, double check that you have sorted correctly, used absolute references, and formatted numbers correctly.
Data Files
To help you in your task, the team has provided you with the file below, which contains data from the online surveys. See the included Data Dictionary for more information on the data.
Great challenge! I used a different method than the =TEXTSPLIT() formula to break the ‘Entry_Result’ column apart. I used the LEFT()/MID()/FIND() functions to achieve the same result.
I found the conditional formatting a little difficult until I realized that the Score_Given column needs to be stored as a number (not text). To fix this, I used the =NUMBERVALUE() formula.
My exact formulas are included in the spoiler below:
Awesome challenge! I got the values for User_Name, Website_Name, and Score_given by using Flash fill (Ctrl+E). I had to adjust the flash fill a few times for it to work.
This challenge was super fun! I loved the process of solving one problem and then using the result in other calculations. I might have done things a little differently but I got the same end result! My formulas got wiped as I followed the instructions but you can look at my file below!
Loved this challenge! The last step was so cool with the conditional formatting! I made mine orange and red in honor of the new Utah Jazz jerseys Challenge1_Hello_World.xlsx (53.7 KB)
What an awesome challenge! Like @Andrew, I opted for the LEFT/RIGHT/MID/FIND approach. I like that this challenge really pushed me to go for XLOOKUP instead of sticking to my go-to VLOOKUP. I’ve converted to the superior XLOOKUP after this challenge.
Such a fun challenge! It was great seeing how my different work in each sheet built off of each other to build the matrix in the end. It has been a while since I have last used the sumif function so I enjoyed the refresher on that. Thanks for the making the challenge! Challenge1_Hello_World.xlsx (56.7 KB)
This was a really cool challenge. I had some fun I never knew I could have on Excel while learning some new things along the way. Challenge1_Hello_World.xlsx (67.6 KB)
Loved the challenge! Great kick off for the website! Instead of using TEXTSPLIT, I copied the column, pasted it after the Concatenated_ID column, and used the Text to Columns data tool under the Data menu tab. Same idea as TEXTSPLIT.
I also kept the leading zero for the Name_ID and Concatenated_ID, which took some digging on the internet. It turns out you can use (for Concatenated_ID column) =CONCAT(G2,TEXT(H2,“00”)) to do so. The “00” specifies how many digits you want, so if wanted a leading zero for the number 18 you could use “000” and it would give you “018”. Cool functionality that I didn’t know, but could be useful in some cases. This works even if the number column is in number format.
Creative challenge! I too relied more on left, mid, right, and find to do the data parsing. It did bite me on the names that received 100 points though because I only took the right two digits
Other than that, a well-written challenge that had a fun reveal at the end.
I just decided to start the weekly challenges from the beginning to make sure that I didn’t miss any. This was a great way to be introduced to TechHub.Training! I especially loved the interaction between the different tabs and the plot twist at the end. Well done on the challenge design!
To start off, I created some empty columns and pasted over the text in the user name column. used text to columns and deleted the random columns because I parsed at the space. Then I used xlookup to fill in the other formats. For the matrix, I used a concat within an xlookup. Then finally to find a pattern, then i summed up the columns and xlookupd values to see that Jae dubbed it up.
