Use an Array Formula to Find Average Returns on Investment

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 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" 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" formulas 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.