Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Format as a Table the range A 7 through H 1 7 using a Table style of your choice ( see Insert | Tables |

Format as a Table the range A7 through H17 using a Table style of your choice (see Insert | Tables | Table) and name the table PledgeAmount (see Table Design | Properties | Table Name).
Rename the Sheet1 tab as Annual Pledge List and color the tab gold (or similar color).
Use the VLOOKUP function for the grade value in column H to determine the letter grade that corresponds to the GPA in column G using the Grade Table in the range $J$7:$K$20. Enter the appropriate VLOOKUP function in cell H8 and copy it to the range H9:H17.
Highlight the Table in A7:H17, select the Total Row option (see Table Design | Table Style Options), and use the drop-down functions in this total row to determine:
The maximum for the Age
The sum for the Pledge Amount
The average for the GPA
The count for the Grade
Use the SUMIF and COUNTIF functions to determine the totals and counts for the Pledge amount with each Gender in the range C21:C24.
Sort the Table by the First Level as Gender (Ascending Order) and Second Level as GPA (Descending Order).(See Home | Sort & Filter | Custom Sort)
Filter the Table by the Gender of Females and Pledge Amount greater than $5000.
Insert a Pivot Table using the data in the table (Insert | PivotTable). Format as follows:
Change the sheet name to Pivot Table, color the tab to green, and move the tab so it is to the right of the Annual Pledge List worksheet.
Set the fields in the Pivot Table as follows:
Filters=Gender
Rows=LName
Columns=Age
Values=Pledge Amount.
Filter the Pivot table for Female.
Change cell B3 to Age and A4 to Last Name.
Apply a Pivot Table Style.
Set all monetary data within the Pivot Table to the number style of Currency without any decimal places (Hint: Do Not Include the Age Field).
Add a Clustered Bar Pivot Chart and change the style to one of your choice.
Delete any unused worksheets.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Information Modeling And Relational Databases

Authors: Terry Halpin, Tony Morgan

2nd Edition

0123735688, 978-0123735683

More Books

Students also viewed these Databases questions

Question

=+. Let 2 be the unit square [(x, y): 0 Answered: 1 week ago

Answered: 1 week ago