Using Excel Total row and Sort & filter options, display the following statistics. Hint: You will need to change your data to a table format. Enter your answers/formulas in cells M2:M4 on the Customer Survey sheet. (Show your results with zero decimal places)

Average age of Undisclosed Gender in Owen Sound who spent money on Gaming. [ No one in Owen sound as undisclosed gender ]

Create a header to include your

First and Last Name on the left,

the answer to question (a) on the right and

the current date filed in the center, on the Customer Survey worksheet.

Average age of males likely to purchase games in the city found to have the highest population.

[ Toronto has the most highest population. Average of ( b11, b48, b237 ) ]

Average age of all genders in all locations.

[ =AVERAGE(Table1[Age]) ]

On the Customer Survey sheet, convert your table back to a data range and then create a pivot table to be placed on the Pivot Table & Chart worksheet starting at cell A1, which will display each of the following criteria.

Save the information a 3D column pivot chart on the same worksheet placing the top left corner of the chart in cell G3. Set all monetary values to Accounting format.

City with the maximum household income total

Average population of all cities

The city where most money was spent on gaming.

On the Customer Survey sheet,

change your date to a table and

sort your data by Customer ID smallest to largest,

Using an excel functions/formula determine the household income and location for the following customer IDs.

Place your answers/formulas in cells M6:M10. Be sure to use absolute cell reference when necessary. Hint: You will need to use a lookup function.

60096

60262

60230

60166

80276

Filter the Pivot Chart you created in step 2, to display only the above 5 cities to market your new game in.

On the Customer Survey worksheet, use the lookup table found in K29:U32 to locate the average game rating related to the locations listed below. Place your answers/formulas in cells M12:M16 Hint: You will need to use a Lookup function.

Thunder Bay

Timiskaming Shores

Kitchener

Brantford

Brant

On the Customer Survey worksheet, use the lookup table found in K29:U32 to locate the average household income related to the locations listed below. Place your answers/formulas in cells M18:M22 Hint: You will need to use a Lookup function.

Pickering

Kitchener

Haldimand County

Quinte West

Niagara Falls

Apply the subtotal feature to your data range (A1:I301), to show each city by game rating. Hint: you must convert your data back from Table to Range. Collapse the cities to display only the subtotals and not the summary of each.

Remove all subtotals, and then create a formula to display the Most, Average, and Median game ratings placing your answers in cell M24:M26

Using the data collected, explain which city you will choose to market your game and why.

Insert 2 rows above your Data and create a logo for your game placing it in Cell A1:B4.