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.
本练习是课程的一部分
Google Sheets 中级
练习说明
- In cell
J2, get the uniqueAreas. - In column
K, rows2to9, useAVERAGEIF()to calculate the average count ofBrush-footeds by Area. Pass it the area data as absolute addresses, the unique areas, and theBrush-footedcount data as absolute addresses. - In column
H, join the average counts back to the original dataset.VLOOKUP()takes 4 arguments. It needs theAreafrom columnA, the data range of the table you just created (J2toK9) 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()columnGis blank, take the value from columnH, else take the value from columnG.
