Get startedGet started for free

Simple imputation

If you have missing data, you can use a set of techniques known as imputation to substitute guesses for those values. A very simple form of imputation is to substitute the average of the non-missing values for that group.

This technique involves 3 steps: use AVERAGEIF() to get the group averages, VLOOKUP() to join these back to the original dataset, and ISBLANK() to locate missing values.

The dataset has been edited so some Brush-footed counts are missing.

This exercise is part of the course

Intermediate Google Sheets

View Course

Exercise instructions

  • In cell J2, get the unique Areas.
  • In column K, rows 2 to 9, use AVERAGEIF() to calculate the average count of Brush-footeds by Area. Pass it the area data as absolute addresses, the unique areas, and the Brush-footed count data as absolute addresses.
  • In column H, join the average counts back to the original dataset. VLOOKUP() takes 4 arguments. It needs the Area from column A, the data range of the table you just created (J2 to K9) as absolute addresses, the column in the that table that contains the averages (2), and whether or not that table is sorted (FALSE).
  • In column I, IF() column G is blank, take the value from column H, else take the value from column G.

Hands-on interactive exercise

Turn theory into action with one of our interactive exercises

Start Exercise