MulaiMulai sekarang secara gratis

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.

Latihan ini adalah bagian dari kursus

Intermediate Google Sheets

Lihat Kursus

Petunjuk latihan

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

Latihan interaktif praktis

Ubah teori menjadi tindakan dengan salah satu latihan interaktif kami.

Mulai berolahraga