Question: c. Fill out B2:B5 by combinedly using Data Validation, conditional formulas, INDEX/MATCH, MAX, and AVERAGEIF(s) In cell B2, use data validation to create a dropdown

c. Fill out B2:B5 by combinedly using Data Validation, conditional formulas, INDEX/MATCH, MAX, and AVERAGEIF(s) In cell B2, use data validation to create

c. Fill out B2:B5 by combinedly using Data Validation, conditional formulas, INDEX/MATCH, MAX, and AVERAGEIF(s) In cell B2, use data validation to create a dropdown list using data in "Club" column (make sure the list is free of duplicated values) In cell B3, use MAX \& IF formula to find the highest overall mark among players of each football club In cell B4, use XLOOKUP or INDEX-MATCH to find the name of player having highest overall mark corresponding to each football club In cell B5, use AVERAGEIF(s) to find average of wage paid per foodball club \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline & A & B & C & D & E & F & G & H & 1 & J & K & L & M & N & 0 \\ \hline 1 & & & & & & & & & & & & & & & \\ \hline 2 & Football Club & & \multicolumn{2}{|c|}{ Use data validation } & & & & & & & & & & & \\ \hline 3 & Highest Overall Mark & & \multicolumn{3}{|c|}{ Use MAX \& IF formulas } & & & & & & & & & & \\ \hline 4 & \multicolumn{2}{|l|}{ No.1 Player based on Overall } & \multicolumn{3}{|c|}{ Use XLOOKUP/INDEX/MATCH } & & & & & & & & & & \\ \hline 5 & Average wage paid & & \multicolumn{2}{|c|}{ Use AVERAGEIF } & & & & & & & & & & & \\ \hline \multicolumn{16}{|l|}{6} \\ \hline 7 & & & & & & & & & & & & & & & \\ \hline 8 & # & Name & Age & Nationality & Overall & Potential & Club & Wage & Preferred Foot & Body Type & Position & Height & Weight & HeightInch & BMI \\ \hline 9 & & L. Messi & 31 & Argentina & 94 & 94 & 4 FC Barcelc & 565000 & Left & Messi & RF & 57 & 159 & & \\ \hline 10 & & Cristiano I & 33 & Portugal & 94 & 94 & 4 Juventus & 405000 & Right & C. Ronaldo & ST & 62 & 183 & & \\ \hline 11 & & Neymar Jr & 26 & Brazil & 92 & 93 & Paris Sain & 290000 & Right & Neymar & LW & 9' & 150 & & \\ \hline 12 & & De Gea & 27 & 7 Spain & 91 & 93 & Manchest & 260000 & Right & Lean & GK & 64 & 168 & & \\ \hline 13 & & K. De Bruy & 27 & 7 Belgium & 91 & 92 & 2 Manchest & 355000 & Right & Normal & RCM & 511 & 154 & & \\ \hline 14 & & E. Hazard & 27 & 7 Belgium & 91 & 91 & Chelsea & 340000 & Right & Normal & LF & 58 & 163 & & \\ \hline 15 & & 7 L. Modri & 32 & 2 Croatia & 91 & 91 & L Real Madr & 420000 & Right & Lean & RCM & 58 & 146 & & \\ \hline 16 & & 3 L. Surez & 31 & Uruguay & 91 & 91 & LC Barcelc & 455000 & Right & Normal & RS & 60 & 190 & & \\ \hline 17 & & Sergio Rar & 32 & Spain & 91 & 91 & Leal Madr & 380000 & Right & Normal & RCB & 60 & 181 & & \\ \hline 18 & & J. Oblak & 25 & Slovenia & 90 & 93 & Atltico N & 94000 & Right & Normal & GK & 62 & 192 & & \\ \hline 19 & & R. Lewand & 29 & Poland & 90 & 90 & FC Bayern & 205000 & Right & Normal & ST & 60 & 176 & & \\ \hline 20 & & 2T. Kroos & 28 & 3 Germany & 90 & 90 & Real Madr & 355000 & Right & Normal & LCM & 60 & 168 & & \\ \hline \end{tabular}

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!

PREPARATION Read all instructions thoroughly before attempting this assignment Read an understand the data dictionary before attempting this assignment Do not add or remove any columns or rows Do not...

Requirements: 1 Calculate Tiger Mountain Gelato's product cost per pint under absorption costing and variable costing 2 Calculate the balance in Finished Goods Inventory on May 31, 2018, using...

The Nelson Company is planning a new division to make a specialised product. This is the only product that the division will make. As a financial analyst in the planning department, youve been asked...

Hi Erylee88, I would like to utilize your service again. Same format different questions. Please find attached document for your review. Let me know. Thank you, Sam...

i have attache the excel assignment. can you please complete this and help me out? Instructions: The following assignment is a 4-part tutorial on more advanced and useful techniques in Excel for data...

Home Insert Draw Page Layout Formulas Data Review View Tell me Calibel (Body 11 ' ' Wrap Text General BIU Merge Center $ % 9 *all-23 Call Conditional Format Formatting Table H19 H M N 0 4 Statistics...

Use EXCEL only please, also make a data analyst excel file thanks! EntertainmentTech Research Analyst - 50% You have been hired to work as a research analyst at Entertainment Tech. EntertainmentTech...

A Step Up Construction A Step Up Construction has asked you with compiling a report that details storage location information, contracts, inventory, and location management. To complete the task, you...

Reinforce Your Skins 119 EA4-R2 Edit an Inventory Listing and Create a PivotTable for Housewares Kingdom In this exercise, you will compile summary data based on an inventory Listing for Housewares...

Kasson Company purchased an asset for $500,000. The asset was assigned a 7-year life with no salvage value. The asset is classified in the MACRS 5-year property class and for tax purposes will be...

Show that the ratio of the X and y components of the field as a function of position, is Ex/Ey = - I[1+ (El/Er) exp {I (Kl-Kr) z} ]/ [1- ( El/ Er) exp { i (Kl-Kr)z } ]

7. Write an IfThenElse statement that displays the value 25 in the lblShipping control when the strState variable contains the string Alaska (in any case); otherwise, display the value 15.

please answer only if you know programming language is c++ For this lab, your objective is to define the functions below. Define the void function SwapNodes () that takes three generic type Node...