Skip to content

4. Use More Data to Improve Your Model
attachment
Students can submit work

Playback Speed:
Transcript

In the last video, you created an array formula that takes a user’s rating for item one, subtracts it from the rating for item two, and calculates the average of those differences.

This calculation will enable you to develop a model that can help you predict how the user will rate other items.

In this video, you will apply the array formula to the other items in your spreadsheet.

To start, click in the cell with the array formula.

Then, drag the cell handle to the next cell.

The array formula is now repeated in the cell, but the cell addresses change relative to their position in the spreadsheet.

These are called “relative cell references.”

This formula subtracts the ratings for the second and third items, not the first and second items.

But the goal is to subtract the first item’s rating from the second, third, fourth, and fifth item’s ratings.

To fix the problem, create “absolute cell references.”

Absolute cell references do not change as a formula is copied into other cells.

To create absolute cell references, add dollar signs before the column letter and row number in the original formula.

Then, copy the revised formula to the other columns in the spreadsheet.

Good work.

Now the first cell reference changes, but the second does not.

Now you know the average difference between a user’s rating for the first item and the other items in your training data set.

Move on to the next video to try out your model using the test data.

Now, it’s your turn: Create absolute cell references for the first item’s cell address.

And copy the array formula to calculate the average difference for the other items in your spreadsheet.

Next
Instructions
  1. Create absolute cell references for the first item’s cell address.
  2. Copy the array formula to calculate the average difference for the other items in your spreadsheet.