Question
ASAP HELP PLEASE!!!! 13. Alex has created an area in the range J3:K8 for looking up data in the Clients table. First, he wants to
ASAP HELP PLEASE!!!!
13. Alex has created an area in the range J3:K8 for looking up data in the Clients table. First, he wants to find the name of the client by looking up the client ID. He has already entered the client ID in cell K3. Look up the client name by using a MATCH formula to modify the INDEX formula as follows:
In place of the 165 after the structured reference to the Clients table, insert the MATCH function.
Match the value in cell K3, and look it up using a structured reference to the Client ID column of the Clients table.
Return an exact match.
15. The third calculation Alex wants to make is to determine the total payments for the policy type of the client he is looking up, which now appears in cell J6. Calculate the total payments for a policy type as follows:
In cell K7, begin to enter a formula using the DSUM function.
Use structured references to the [#Headers] and [#Data] in the Clients table to specify the formula database.
Use a structured reference to the Payment field header to specify the field to summarize.
Use the values in the range J5:J6 as the criteria.
16. Alex also wants to identify the number of a policies he has sold of a specified type. Calculate this information as follows:
In cell K8, begin to enter a formula using the DCOUNTA function.
Based on the headers and data in the Clients table, and using structured references, count the number of values in the Policy Type column that match the criteria in the range J5:J6.
20. Return to the Clients worksheet. In the range K10:K13, Alex wants to display the total payments received for each type of policy. Display this information for Alex as follows:
In cell K10, enter a formula that uses the GETPIVOTDATA function.
Using "Payment" as the data field, extract data from the PivotTable starting in cell A3 on the Monthly Payments worksheet. Use an absolute reference to the cell.
Select the grand total quantity amount for Auto policies by using "Policy Type" as the field1 argument and the name of the policy type (cell J10 of the Clients worksheet) as the item1 argument.
Fill the range K11:K13 with the formula in cell K10, filling the range without formatting.
It said that most of my formulas were wrong.
In the Clients worksheet, the formula in cell K4 should use the value 2 as the column_num argument of the outer INDEX function. |
In the Clients worksheet, the formula in cell K7 should use structured references to the [#Headers] and [#Data] in the Clients table as the database argument. |
In the Clients worksheet, the formula in cell K8 should use structured references to the [#Headers] and [#Data] in the Clients table as the database argument |
In the Clients worksheet, the formula in cell K10 should use an absolute reference to cell A3 in the Monthly Payments worksheet as the pivot_table argument. |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started