Skip to content

4. Use an Array Formula to Find Average Returns on Investment

Playback Speed:

In this video, you’ll use an array formula to find the average return on investment for one category: genre, director, or actor.

By calculating the average ROI your group will be be able to analyze each element of a movie idea.

For example, if you’re deciding between an action or comedy movie, or if you want to know which actor is more profitable, you’ll be able to compare the average return on investment for each.

Currently, your data for genre, director, and actor is spread across many rows and columns.

To get the average return for one item, like comedy, you could first check if it’s in each row.

Then, to get an average, add the returns on investment for each “Comedy” row, and divide by the number of times “Comedy” appears in the sheet.

You would then perform these steps for every item in the sheet: Action, Comedy, Drama, and so on.

Luckily, you can use array formulas to do this math quickly, easily, and accurately.

To start, go to the sheet for your category.

Then, label column C “Average Return on Investment.”

Start your first formula in C2.

Type “equals arrayformula” and press enter.

Within the parentheses, type “average” and another open parentheses.

Then, type “if” and one more open parentheses.

This formula operates in much the same way as the “SUM-IF” formula you used in previous activities.

But this time, instead of checking one column in the IF statement, you will check multiple columns.

In the Movie Data sheet, select the range of data for the items you’re checking for -- in this case, the genre columns.

Then, return to the Genres sheet, type “equals” and select the cell that contains the data you’re looking for.

In this example, that’s cell A2, “Comedy.”

This now reads, “if COMEDY” is in columns D or E, then…” Next, type a comma.

Select the ROI column from the movie data sheet, close the parentheses and press enter to finish the formula.

Now, this array formula checks columns D and E to see if they contain the item “CATEGORY.”

Then, the formula calculates the average ROI for those items.

Finally, copy this formula to the rest of the cells in the column.

Change the column format to “percentage.”

This is a complex formula, and it can be difficult to ensure you’ve selected the right cells.

To check your work, click into a cell with the formula.

Verify that the linked cells match the help text.

You can also select “view” and “show formulas” to verify that your formula copied correctly.

Now that you have the average ROI for each item in your category, you can compare the return on horror movies and action movies, for example.

You can see which actors have a really high -- or a really poor -- return on investment.

With this data, your group can make a more informed decision about which movie idea you’d like to select.

Now, it’s your turn: In your category sheet, create a column for average ROI.

Use an array formula to calculate the average return on investment for the first item in your category.

Copy the Average IF array formula to the rest of the spreadsheet and change the number format to percent.

Then, move on to the next video to wrap up this activity.

Next arrow_forward
  1. In your category sheet (genre, actors, or directors), create a column for average ROI.
  2. Use an array formula to calculate the average ROI for the first item in your category.
  3. Copy the Average If array formula.
  4. Change the number format to percent.
  5. Then, move on to the next video.