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

Este ejercicio forma parte del curso

Intermediate Google Sheets

Ver curso

Instrucciones del ejercicio

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

Ejercicio interactivo práctico

Pon en práctica la teoría con uno de nuestros ejercicios interactivos

Empezar ejercicio