Courses/Computer Science/CPSC 203/CPSC 203 Template/Labs Template/TA Examples for Spreadsheets/Navneet: Week 2 - Lab 1

Example. The spreadsheet below contains movies sales data for 2008 (including movies that opened in 2007 and continued into 2008). Download the spreadsheet and complete the following tasks, as well as answer the following questions:

• Tasks:
  • Create a chart which displays

• • Implement a custom calculation that takes the cost of a single movie ticket, adds a $1.50 surcharge fee, and taxes of 5%
    • Note: remember the order of mathematical operations when evaluating an expression, and use circular brackets when required for grouping subexpressions
  • Implement a nested If-Then statement for each movie listed that incorporates the following; Blockbuster is greater than or equal to sales of $220 000 000, Hit is greater than or equal to sales of 150 000 000, and Success if less than sales of 150 000 000
    • Note - don't forget to put double quotes " " around a string statement, e.g. "Blockbuster"
  • Summarize data for Total, Average, and Max movie sales in a pivot table, organized with movie studios along the top row and movie genres along the left column
  • Apply a lookup function to each movie, using the AutoFILL feature, to determine if it was a success, a hit, or a blockbuster (use the same ranges as defined in task two)
    • Note: use absolute referencing (the symbol $ is used for absolute referencing in Excel) when using the AutoFILL feature to reference the same set of cells (where required) for each lookup function
• Questions:
  • What are the similarities and differences between If-Then statements and the Lookup function in Excel

Media: Navneet_-_Spreadsheet_Example_2_-_2008_Movie_Sales.xls