Build a pivot table which calculates the average and maximum sales by department You will have...

Build a pivot table which calculates the average and maximum sales by department

You will have to combine the data using VLOOKUP or MATCH/INDEX. Note any observations you see regarding trends in the results.

Use the data below to solve the problem. List steps and directions step by step with any formulas, what has been clicked on, and any other important information to solve the problem using Excel. Thank you.

Sales Data
Loyalty ID #	Sale Total	Department
1391	$ 514.72	Clothing
1804	$ 109.85	Housing
1473	$ 328.42	Housing
1847	$ 124.78	Athletic
1586	$ 79.84	Other
1243	$ 147.32	Luggage
1117	$ 235.15	Clothing
1995	$ 89.20	Hardware
1591	$ 38.53	Luggage
1919	$ 53.63	Hardware
1957	$ 273.65	Other
1397	$ 58.50	Athletic
1118	$ 184.85	Luggage
1354	$ 104.02	Athletic
1414	$ 36.25	Other
1714	$ 263.19	Clothing
1826	$ 75.42	Athletic
1591	$ 200.31	Hardware
1387	$ 71.77	Other
1147	$ 225.76	Other
1298	$ 168.35	Athletic
1045	$ 41.81	Clothing
1022	$ 66.31	Other
1290	$ 12.87	Other
1059	$ 309.17	Athletic
1522	$ 64.22	Other
1533	$ 210.60	Hardware
1266	$ 286.38	Clothing

Expert Solution

Answer-

The image below shows the Average sales and maximum sales by each department-

The steps followed are as follows-

1. Converted the Sale totals in number format by removing $ and spaces. See the below screenshot-

2. Created a pivot table with following fields-

Department names in Row Labels field
Sum of Sale Total, Average of Sale Total and Max of Sale Total in Values field

See the screenshot below-

ekkarill92 answered 6 months ago

How do you design a pivot table in Excel?

Create a C++ program which will accept an unlimited number of scores and calculates the average...

Create a C++ program which will accept an unlimited number of scores and calculates the average score. You will also need to prompt for the total points possible. Assume each test is worth 100 points. Requirements. • Enter the Student ID first, then prompt for grades. Keep prompt- ing for grades until the client enters ’calc’. That triggers final pro- cessing. • Make your code as reliable as possible. • Make your program output easy to read. • You cannot...

1. When you used a data model to create a Pivot Table, you receive some errors....

1. When you used a data model to create a Pivot Table, you receive some errors. Which is the likely reason? Select an answer: Your Pivot Table was created from an Excel table. Your Pivot Table contains empty cells. Your Pivot Table did not update changes to your Excel table. Your relationships were not properly defined. 2. When you are creating relationships between tables, the unique values for each row are known as the _____. Select an answer: index data...

The function print_mean() that you wrote in the previous lesson calculates an average value and prints...

The function print_mean() that you wrote in the previous lesson calculates an average value and prints it on the screen. Change this function so that instead of printing the average it returns the average.In order to calculate the sum, you won't need to form a loop; call the function column_sum() instead. # columns are [0]title [1]year [2]rating [3]length(min) [4]genre [5]budget($mil) [6]box_office_gross($mil) oscar_data = [ ["The Shape of Water", 2017, 6.914, 123, ['sci-fi', 'drama'], 19.4, 195.243464], ["Moonlight", 2016, 6.151, 110, ['drama'],...

I have extra columns with data showing up in my pivot table. How can I get...

I have extra columns with data showing up in my pivot table. How can I get rid of the extra columns?

A random sample of 81 credit sales in a department store showed an average sale of...

A random sample of 81 credit sales in a department store showed an average sale of $68.00. From past data, it is known that the standard deviation of the population is $27.00. What is the 99% confidence interval of the population mean? (Round to two decimal places)

Triton Company's copy department, which does almost all of the photocopying for the sales department and...

Triton Company's copy department, which does almost all of the photocopying for the sales department and the administrative department, budgets the following costs for the year, based on the expected activity of copies: Salaries (fixed) $91,250 Employee benefits (fixed) 10,000 Depreciation of copy machines (fixed) 10,000 Utilities (fixed) 5,000 Paper (variable, 1 cent per copy) 50,000 Toner (variable, 1 cent per copy) 50,000 The costs are assigned to two cost pools, one for fixed and one for variable costs. The...

The Apex Company’s copy department, which does almost all of the photocopying for the sales department...

The Apex Company’s copy department, which does almost all of the photocopying for the sales department and the administrative department, budgets the following costs for the year, based on the expected activity of 5,000,000 copies: Salaries (fixed) $100,000 Employee benefits (fixed) 12,500 Depreciation of copy machines (fixed) 10,000 Utilities (fixed) 5,000 Paper (variable, 1 cent per copy) 50,000 Toner (variable, 1 cent per copy) 50,000 The costs are assigned to two cost pools, one for fixed and one for variable...

Salespeople for a car sales company have an annual sales average of $225,000, with a standard...

Salespeople for a car sales company have an annual sales average of $225,000, with a standard deviation of $18,000. What percentage of the salespeople will make the following sales? Assume the sales follow a normal distribution. A. More than $25,000 B. Less than $190,000 C. Between $200,000 and $240,000

3. A random sample of 81 credit sales in a department store showed an average sale...

3. A random sample of 81 credit sales in a department store showed an average sale of $68.00. From past data, it is known that the standard deviation of the population is $27.00. a. First, verify that the Central Limit Theorem condition(s) have been met, then construct a 90% confidence interval of the population mean. b. Construct a 99% confidence interval of the population mean. c. Justify in your own words the difference in the interval lengths from parts a...

Question