Create Pivot Tables in the places indicated on the Pivot

Question
Create Pivot Tables in the places indicated on the Pivot Tables sheet:

Average Loans by State:

Show the average loan amount for each state.

Change the Row Labels field to the attribute name for that column.

Format numbers with commas, 2 decimals, and no dollar sign.

Sort Average Loan Amount highest to lowest.

Loans by Purpose:

Show the number of loans for Charged Off, Fully Paid, and Grand Total in separate columns.

Change Count Of field to the Pivot Table name (title) and Row Labels field to the attribute name for

that column.

Format numbers with commas, no decimals, and no dollar sign.

Sort Total Loan Count highest to lowest.

Loans by Monthly Payment:

Show the number of loans for Charged Off, Fully Paid, and Grand Total in separate columns.

Change Count Of field to the Pivot Table name (title) and Row Labels field to the attribute name for

that column.

Format numbers with commas, no decimals, and no dollar sign.

Group Monthly Payments into groups of 100.

Transform alphabetical data to numerical (so that you can run a correlation and regression):

Create VLOOKUP formulas in the three empty columns on the VLOOKUP sheet:

Loan Status.

Home Ownership.

Loan Grade.

Use the information on the Reference Tables sheet to get the numerical data values for each of the

three attributes.

Keep the VLOOKUP formulas on the VLOOKUP sheet because they will be graded.

Copy and Paste As Values all the columns from the VLOOKUP sheet onto the Numerical sheet,

then delete the three columns with alphabetical data.

The Numerical sheet should then contain six attributes that are all numeric.

Run a Correlation:

Use the data from the Numerical sheet.

Create the Correlation for all variables at the same time on the Correlation sheet.

Question image 1