Billion Dollar Movies: Practice with Excel

 

Practice Objectives
  • Use Excel formulas and functions to organize, interpret, and analyze movie-related data.
  • Apply IF and VLOOKUP formulas to classify films and retrieve information from related data tables.
  • Use movie industry data to practice making business-oriented observations about box office performance, ratings, and trends.

In this practice set, you will use a movie-industry dataset to apply the Excel skills from Chapter 3 in a more realistic business context. You will retrieve information with VLOOKUP, classify results with IF and nested logical functions, and use conditional formatting to make patterns easier to see. As you work focus on what the results mean: identify highly rated movies, compare runtimes, find actors, or spot trends that could support marketing and financial decisions.

1. VLOOKUP

Based on the Movies sheet in the Hollywood Movies Chapter Practice file, use VLOOKUP to return Actor 3 for Captain America: Civil War.

Formula: =VLOOKUP("Captain America: Civil War",Movies!$C$2:$F$66,4,FALSE)

Answer: Scarlett Johansson

Simple solution: The formula searches for Captain America: Civil War in the first column of the selected lookup table and returns the value from the fourth column, which is Actor 3.

Formula breakdown:

=VLOOKUP("Captain America: Civil War",Movies!$C$2:$F$66,4,FALSE)

  • = tells Excel that this is a formula.
  • VLOOKUP is the function. It searches vertically down the first column of a table.
  • ( and ) enclose the information, called arguments, that VLOOKUP needs.
  • "Captain America: Civil War" is the lookup value. This is the value Excel is trying to find. Quotation marks are used because the movie title is text.
  • The comma , separates one argument from the next.
  • Movies! tells Excel to look on the Movies worksheet. The exclamation point ! separates the worksheet name from the cell range.
  • $C$2:$F$66 is the table array, or the range Excel will search.
  • The colon : means through, so C2:F66 means all cells from C2 through F66.
  • The dollar signs $ make the range an absolute reference. This keeps the lookup table from moving if the formula is copied.
  • 4 is the column index number. Excel counts columns from the left side of the table array:
    • Column 1 = Title
    • Column 2 = Actor 1
    • Column 3 = Actor 2
    • Column 4 = Actor 3
  • FALSE tells Excel to find an exact match for the movie title.

In plain English:

Find Captain America: Civil War in the Movies table, return Actor 3, and only accept an exact title match.


2. IF Function

Based on the Movie Details sheet in the Hollywood Movies Chapter Practice file, label a movie Short if its runtime is less than 120 minutes and Longer otherwise. What would the formula return for Zootopia, which is 108 minutes?

Formula: =IF(E2<120,"Short","Longer")

Answer: Short

Simple solution: The formula checks whether the runtime is less than 120 minutes. Because 108 is less than 120, Excel returns Short.

Formula breakdown:

=IF(E2<120,"Short","Longer")

  • = tells Excel this is a formula.
  • IF is the function. It checks whether a condition is true or false.
  • E2<120 is the logical test.
  • E2 is the cell containing the movie’s runtime.
  • < means less than.
  • 120 is the cutoff used in the rule.
  • The first comma , separates the logical test from the result Excel should return when the test is true.
  • "Short" is the value if true. Excel returns this when the runtime is less than 120.
  • "Longer" is the value if false. Excel returns this when the runtime is 120 minutes or more.
  • Quotation marks " " tell Excel that Short and Longer are text rather than cell references or function names.
  • ( and ) contain the three IF arguments.

The basic IF structure is:

IF(logical test, value if true, value if false)

In plain English:

If the runtime in E2 is less than 120 minutes, display Short. Otherwise, display Longer.

For Zootopia, 108 is less than 120, so the result is Short.


3. IF with Multiple Conditions

Based on the Movie Details sheet in the Hollywood Movies Chapter Practice file, label a movie Recommend if its IMDb rating is at least 8.0 and its runtime is no more than 150 minutes. What would the formula return for Spider-Man: No Way Home, which has an IMDb rating of 8.1 and a runtime of 148 minutes?

Formula: =IF(AND(C2>=8,E2<=150),"Recommend","Skip")

Answer: Recommend

Simple solution: The formula checks two conditions. The rating of 8.1 is at least 8.0, and the runtime of 148 minutes is no more than 150. Because both conditions are true, Excel returns Recommend.

Formula breakdown:

=IF(AND(C2>=8,E2<=150),"Recommend","Skip")

  • = tells Excel this is a formula.
  • IF tells Excel to make a decision based on whether a condition is true or false.
  • AND lets the formula test more than one condition.
  • C2>=8 is the first condition.
  • C2 contains the IMDb rating.
  • >= means greater than or equal to.
  • 8 is the minimum rating required.
  • The comma inside AND separates the first condition from the second condition.
  • E2<=150 is the second condition.
  • E2 contains the runtime.
  • <= means less than or equal to.
  • 150 is the maximum runtime allowed.
  • AND(C2>=8,E2<=150) is true only when both conditions are true.
  • The comma after the closing ) of AND separates the logical test from the IF result.
  • "Recommend" is the value if true.
  • "Skip" is the value if false.
  • Quotation marks show that Recommend and Skip are text.
  • The inner parentheses belong to AND, while the outer parentheses belong to IF.

The structure is:

IF(AND(condition 1, condition 2), value if true, value if false)

In plain English:

If the movie has a rating of at least 8 and a runtime of 150 minutes or less, display Recommend. Otherwise, display Skip.

For Spider-Man: No Way Home:

  • 8.1 is at least 8.0.
  • 148 is no more than 150.

Both conditions are true, so Excel returns Recommend.


4. Conditional Formatting

Based on the Movie Details sheet in the Hollywood Movies Chapter Practice file, apply conditional formatting to highlight movies with runtimes under 110 minutes. Would Zootopia 2, which is 108 minutes, be highlighted?

Rule: Cell Value < 110

Answer: Yes

Simple solution: The conditional-formatting rule highlights values below 110. Because 108 is less than 110, Zootopia 2 would be highlighted.

Rule breakdown:

Cell Value < 110

  • Cell Value tells Excel to evaluate the actual number stored in each selected cell.
  • < means less than.
  • 110 is the cutoff value.
  • Any runtime below 110 satisfies the rule.
  • A runtime of exactly 110 would not be highlighted because the rule uses <, not <=.
  • Conditional formatting changes the appearance of the cell, not the underlying value.
  • The formatting updates automatically if the value later changes.

In plain English:

Highlight the cell if its runtime is less than 110 minutes.

Because Zootopia 2 has a runtime of 108 minutes, it meets the rule and would be highlighted.

License

Icon for the Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License

Billion Dollar Movies: Practice with Excel Copyright © 2020 by Emese Felvegi; Barbara Lave; Diane Shingledecker; Julie Romey; Noreen Brown; Mary Schatz; OpenStax; Saylor Academy; University of Minnesota Libraries; and Robert McCarn is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License, except where otherwise noted.

Share This Book