$9$ The top$-$middle section of the spreadsheet is designed for summary statistics for multiple conditions. You will calculate the number of houses sold for each agent when he or she served as a Dual Agent $($DA$).$ Use mixed references for ranges and the agent code condition in cell J$3.$ Use relative cell references to the agent condition in cell E$2.$ When you copy the formulas, use the paste Formulas options to preserve border formatting.

In cell F$2,$ insert the COUNTIFS function in cell F$2$ to count the number of houses sold by the first agent $($cell E$2)$ who was a Dual Agent $($DA$)($J$3)$ for that house. Use all cell references in the function. Copy the function to the range F$3$:F$4$ and preserve the bottom border for cell F$4.$

$10$

You are ready to calculate the total value of those houses for each agent when he or she served as a Dual Agent $($DA$).$ Use mixed references for ranges and the agent code condition in cell J$3.$ Use relative cell references to the agent condition in cell E$2.$ When you copy the formulas, use the paste Formulas options to preserve border formatting.

In cell G$2,$ insert the SUMIFS function to sum the selling prices of the houses sold by the first agent $($cell E$2)$ who was a Dual Agent $($DA$)($J$3)$ for that house. Copy the function to the range G$3$:G$4$ and preserve the bottom border for cell G$4.$

$11$ Now, you will calculate the highest$-$price house highest$-$price house sold for each agent when he or she served as a Dual Agent $($DA$).$ Use mixed references for ranges and the agent code condition in cell J$3.$ Use relative cell references to the agent condition in cell E$2.$ When you copy the formulas, use the paste Formulas options to preserve border formatting.

In cell $H_2,$ insert the MAXIFS function in cell $H_2$ to display the highest$-$price house sold by the first agent $($cell E$2)$ who was a Dual Agent $($DA$)($J$3)$ for that house. Copy the function to the range $H3$:$H4$ and preserve the borders in the range $H3$:$H4.$

$12$ The Map worksheet contains a list of cities, postal codes, and average house sales. You will insert a map chart to depict the averages visually using the default gradient fill colors.

Display the Map worksheet, select the range B$1.$C$5$ and insert a map chart.

Hint The Insert tab contains the option to insert a map.

Created On: $08/04/2020$

$2$

Exp$19\_$Excel$\_$Ch$07\_$Cap $-$ Real Est

$$

$$