Calculate the Age as the Current Year minus the YR$\_$Built. You must use the YEAR and

NOW formulas together to do this.

$4.$ Perform the following lookup on the $$King County, WA House Prices$$ worksheet.

a$)$ Column T: Use VLOOKUP to retrieve the City from the $$City List$$ worksheet, based on the value in

the ZIP Code column.

$5.$ Perform the following formula calculations on the $$City List$$ worksheet. Note that you will need to use

values stored in the $$King County, WA House Prices$$ worksheet to accomplish this.

a$)$ Column C: Use the AVERAGEIF function to determine the average house age $($in years based on

Column Q in the other worksheet$)$ for each Zip Code. Several Zip Codes do not have recorded

sales, so you will see a divide by zero error for those.

CSCI $5000$: Data Management P a g e $|2$

b$)$ Column D: Use the AVERAGEIF function to determine the average price for each Zip Code $($based

on Column C in the other worksheet$).$ Several Zip Codes do not have recorded sales, so you will

see a divide by zero error for those.

c$)$ Column E: Use the AVERAGEIF function to determine the average square footage for each Zip Code

$($based on Column M in the other worksheet$).$ Several Zip Codes do not have recorded sales, so

you will see a divide by zero error for those.

d$)$ Column F: Use the AVERAGEIF function to determine the average lot size $($in acres$)$ for each Zip

Code $($based on Column O in the other worksheet$).$ Several Zip Codes do not have recorded sales,

so you will see a divide by zero error for those.

e$)$ Column G: Use the COUNTIF function to count the number of sales for each Zip Code $($i$.$e$.,$ count

the number of times the ZIP Code appears in Column S in the other worksheet$).$