LoslegenKostenlos loslegen

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.

Diese Übung ist Teil des Kurses

Intermediate Google Sheets

Kurs anzeigen

Anleitung zur Übung

  • 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.

Interaktive Übung

In dieser interaktiven Übung kannst du die Theorie in die Praxis umsetzen.

Übung starten