Marketing Analytics Excel Assignment: Essential Formulas Guide

Marketing Analytics Excel Assignment For this assignment, use this dataset and simply answer the following questions, making sure to follow all instructions. Most questions ask for appropriate Excel formulas…..do not forget to include those. For the questions requiring a formula, the formulas are worth 50% of the points for that question. NOTE: some of the questions can be answered with multiple formulas. However, you should use only the formulas discussed in class. Once you have finished, submit BOTH this Word document and your Excel file (clearly showing your work) in Canvas. 1. Sort the dataset by observationid in ascending order. Explain how you did it. No formula needed here. Once you have sorted the dataset, do not sort it again, as it will impact your answers. Answer: 2. Fill in the table below with how many observations are from the Northeast, New York, and Tampa regions. Answer: Region Observations Formula 3. Combine month and year into a new, single cell in Column J, with a hyphen between the combined components. What is in that cell for observationid #476? Formula: Answer: 4. Count the length of the values in totalvolume for instance #794. It will count the decimal and the two decimal places. That is fine. Formula: Answer: 5. Count the number of times California appears in region. Formula: Answer: 1

Marketing Analytics Excel Assignment 6. Count the number of observations from the West region having an average price > $1.00 Formula: Answer: 7. Given the data, predict totalvolume for a hypothetical instance with a price of $1.10. Formula: Answer: 8. Create a new column (Column K) with values of 0 and 1, with 1 being Organic and 0 being Conventional. What is the value for observation #2189? Formula: Answer: 9. Create a column (Column L) that returns TRUE for each observation from 2019 with an averageprice > $0.94. What is the value for observation #1377? Formula: Answer: 10.Create a column (Column M) that returns TRUE for each observation that is either Conventional or has an averageprice < $0.78. What is the value for observation #6618? Formula: Answer: 11.Calculate the second quartile for totalvolume. Formula: Answer: 12.Calculate the third quartile for averageprice. Formula: Answer: 13.Create a column (Column N) providing the rank of all observations on totalvolume. What is the rank of observation #8850? Formula: 2

Marketing Analytics Excel Assignment Answer: 14.Add up the totalvolume of all observations that used supplier C. Formula: Answer: 15.Add up the totalvolume of all observations that are Organic with a price < $1.28. Formula: Answer: 16.Calculate the highest averageprice for those observations that are Conventional and have a totalvolume > $7,500,000. Formula: Answer: Create pivot tables for the following. Take screenshots of each and embed as your answer. 17.totalvolume, broken down by year, with years in rows Answer: 18.totalvolume, broken down by region and year, with regions in rows and years in columns Answer: 19.averageprice, broken down by region and supplierid, with regions and suppliers in rows (screenshot only needs to show the first three regions, Buffalo/Rochester, California, and Great Lakes) Answer: Then, fill out the following tables and create corresponding charts as instructed. Embed a screenshot of the chart below its respective table. Each chart should have a title. 20.Column chart: totalvolume by supplierid Supplierid Totalvolume Formula 3

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Marketing Analytics Excel Assignment 21.Column chart: averageprice by type Type Average Price Formula 22.Column chart: observations by quarter (for 2018 only) Quarter Observations Formula 23.Pie chart: totalvolume by type Type Total Volume Formula 24.Line chart: totalvolume by year Year Total Volume Formula 4

Marketing Analytics Excel Assignment Finally, create the following histogram, and embed it in your answer sheet. No table needed. 25.Histogram on averageprice (4 bins) 5

5413 Excel Assignment

Related Documents