Case Problem 1 Data file weeded for thls Cace Problem: NP ix 7.3. vhex in selempe and math extucation. After the conference, Robert compiles results from a survey to acts a sulde for the neyt conferempe. Wy'll hely Robert generate a report on the conference response. In the Suncy Reuits work shect, the amswers to seven survey questions have been entered in an Excel table named Survey. The responses for the first four questions are the letters a through d, when represent responses from "very satisfied" to "very dissatisfied." The text of the survey questrons is on the 1. Open the NP EX 7-3.xlsx workbook located in the Excel7 Casel folder included with your Data Files, and then save the workbook as NP_EX_7.STEM in the location specified by your instructor. 2. In the Documentation sheet enter your name and the date. 3. In the Survey Results worksheet, in the Workshops column, display text associated with answe to Q1 by clicking cell 16 and inserting the VLOOKUP function to do an exact match lookup w the Q1 field as the lookup value, the survey_lookup table as the lookup table, the second column of that table as the return value, and the range_lookup value set to FALSE. 4. Repeat Step 3 for the Speakers through Meals field, using values of the Q2 through Q4 fields. (Hint: You can use AutoFill to quickly enter the formulas for the Speakers through Meals fields 5. In the School column, display the type of school of each attendee (Public, Private, Online, or Tutor) by clicking cell M6 and inserting the VLOOKUP function to do an exact match lookup values in the Q5 field from the school_lookup table, returning values from the second columr that table, and setting the range_lookup value to FALSE. 6. In the Prior Conferences column, indicate the number of conferences previously attended $$ (0 $$, 2 , and $$ 3+ $$ ) by clicking cell N6 and inserting an approximate match look up of the values in th Q6 field using the conference_lookup table as the lookup table and returning the value from second column of that table. On the Report worksheet, do the following: a. In cell B14, use the COUNTIF function to count the number of records in the Return field from the Survey table that equal "will return." b. In cell B15, calculate the difference between cell B11 and B14. c. In the range B18:B21, use the COUNTIF function to count the number of records of the School field in the Survey table that equal Public, Private, Online, and Tutor. 1. In the range $$ B 24: B 27 $$, use the COUNTIF function to count the number of records in the $$ F $$ Conferences field of the Survey table that equal $$ 0,1,2 $$, and $$ 3+ $$.

Question

Case Problem 1 Data file weeded for thls Cace Problem: NP ix 7.3. vhex in selempe and math extucation. After the conference, Robert compiles results from a survey to acts a sulde for the neyt conferempe. Wy'll hely Robert generate a report on the conference response. In the Suncy Reuits work shect, the amswers to seven survey questions have been entered in an Excel table named Survey. The responses for the first four questions are the letters a through d, when represent responses from "very satisfied" to "very dissatisfied." The text of the survey questrons is on the 1. Open the NP EX 7-3.xlsx workbook located in the Excel7 > Casel folder included with your Data Files, and then save the workbook as NP_EX_7.STEM in the location specified by your instructor. 2. In the Documentation sheet enter your name and the date. 3. In the Survey Results worksheet, in the Workshops column, display text associated with answe to Q1 by clicking cell 16 and inserting the VLOOKUP function to do an exact match lookup w the Q1 field as the lookup value, the survey_lookup table as the lookup table, the second column of that table as the return value, and the range_lookup value set to FALSE. 4. Repeat Step 3 for the Speakers through Meals field, using values of the Q2 through Q4 fields. (Hint: You can use AutoFill to quickly enter the formulas for the Speakers through Meals fields 5. In the School column, display the type of school of each attendee (Public, Private, Online, or Tutor) by clicking cell M6 and inserting the VLOOKUP function to do an exact match lookup values in the Q5 field from the school_lookup table, returning values from the second columr that table, and setting the range_lookup value to FALSE. 6. In the Prior Conferences column, indicate the number of conferences previously attended $$ (0 $$, 2 , and $$ 3+ $$ ) by clicking cell N6 and inserting an approximate match look up of the values in th Q6 field using the conference_lookup table as the lookup table and returning the value from second column of that table. On the Report worksheet, do the following: a. In cell B14, use the COUNTIF function to count the number of records in the Return field from the Survey table that equal "will return." b. In cell B15, calculate the difference between cell B11 and B14. c. In the range B18:B21, use the COUNTIF function to count the number of records of the School field in the Survey table that equal Public, Private, Online, and Tutor. 1. In the range $$ B 24: B 27 $$, use the COUNTIF function to count the number of records in the $$ F $$ Conferences field of the Survey table that equal $$ 0,1,2 $$, and $$ 3+ $$.

UpStudy AI · Accepted Answer

**1. Open and Save the Workbook**  
- Open the workbook named `NP EX 7-3.xlsx` located in the `Excel7 > Casel` folder.  
- Save the workbook using the new filename `NP_EX_7.STEM` in the location specified by your instructor.

**2. Documentation Sheet**  
- Go to the Documentation worksheet.  
- Enter your name and the current date in the appropriate fields.

**3. Inserting VLOOKUP for Workshop Responses**  
- Navigate to the **Survey Results** worksheet.  
- Locate the column labeled **Workshops**.  
- Click the cell (e.g., cell corresponding to the first record for Workshops—assume cell where Q1 response is to be translated).  
- Insert the VLOOKUP function with the following parameters:  
  - **Lookup value:** The cell containing the answer to question Q1.  
  - **Table array:** The named range `survey_lookup` which contains lookup values and associated text.  
  - **Column index number:** Use the second column (which holds the descriptive text) in the lookup table.  
  - **Range lookup:** Use `FALSE` for an exact match.  
  - The formula will be in the form  
    $$
    =\text{VLOOKUP}(\text{Q1 cell}, \; \text{survey\_lookup}, \; 2, \; \text{FALSE})
    $$

**4. Repeating VLOOKUP for Speakers, Meals, etc.**  
- For the **Speakers** through **Meals** fields:  
  - Click the corresponding cell for each field (using Q2 through Q4 answers respectively).  
  - Insert a similar VLOOKUP function as in Step 3, updating the lookup value to point to the corresponding Q2, Q3, or Q4 cell.  
  - You can use the AutoFill feature to copy the formulas from one cell to adjacent cells, then adjust if needed.

**5. VLOOKUP for School Type**  
- In the **School** column on the Survey Results worksheet, select cell $$ M6 $$.  
- Insert a VLOOKUP function with:  
  - **Lookup value:** The cell containing the answer to question Q5.  
  - **Table array:** The named range `school_lookup`.  
  - **Column index number:** 2 (to return the type of school: Public, Private, Online, or Tutor).  
  - **Range lookup:** Set to `FALSE`.  
- The formula will look like:  
  $$
  =\text{VLOOKUP}(\text{Q5 cell}, \; \text{school\_lookup}, \; 2, \; \text{FALSE})
  $$

**6. Lookup for Prior Conferences**  
- Click the cell in the **Prior Conferences** column (starting, for example, at cell $$ N6 $$).  
- Enter a VLOOKUP function set up for an approximate match with:  
  - **Lookup value:** The cell containing the number from Q6 (indicating previous conferences attended).  
  - **Table array:** The named range `conference_lookup`.  
  - **Column index number:** 2 (to return the text $$ 0 $$, 1, 2, or $$ 3+ $$ as specified).  
  - **Range lookup:** Omit an exact match by either setting it to `TRUE` or omitting the parameter so that an approximate match is used.  
- The function may be written as:  
  $$
  =\text{VLOOKUP}(\text{Q6 cell}, \; \text{conference\_lookup}, \; 2, \; \text{TRUE})
  $$

**Report Worksheet Tasks**

**a. Count “will return” Records**  
- On the Report worksheet, select cell $$ B14 $$.  
- Insert the COUNTIF function to count the number of records in the **Return** field of the Survey table equal to “will return.”  
- The formula will be:  
  $$
  =\text{COUNTIF}(\text{Survey[Return]}, "will return")
  $$

**b. Calculate the Difference**  
- In cell $$ B15 $$, calculate the difference between cell $$ B11 $$ and $$ B14 $$.  
- Write the formula as:  
  $$
  =B11 - B14
  $$

**c. Count by School Type**  
- In the range $$ B18:B21 $$ the task is to count the number of records in the **School** field for each school type (Public, Private, Online, and Tutor).  
- In cell $$ B18 $$, for example, you might use:  
  $$
  =\text{COUNTIF}(\text{Survey[School]}, "Public")
  $$  
- Similarly, in cells $$ B19 $$, $$ B20 $$, and $$ B21 $$ replace "Public" with "Private", "Online", and "Tutor" respectively.

**d. Count by Prior Conferences**  
- In the range $$ B24:B27 $$, count the number of records in the **F Conferences** field of the Survey table for each of the values $$ 0 $$, $$ 1 $$, $$ 2 $$, and $$ 3+ $$.  
- In cell $$ B24 $$, use:  
  $$
  =\text{COUNTIF}(\text{Survey[F Conferences]}, 0)
  $$  
- In cell $$ B25 $$:  
  $$
  =\text{COUNTIF}(\text{Survey[F Conferences]}, 1)
  $$  
- In cell $$ B26 $$:  
  $$
  =\text{COUNTIF}(\text{Survey[F Conferences]}, 2)
  $$  
- In cell $$ B27 $$:  
  $$
  =\text{COUNTIF}(\text{Survey[F Conferences]}, "3+")
  $$

Each step follows the instructions provided in the case problem ensuring that survey responses are properly looked up and counted in both the Survey Results and Report worksheets.
**Steps to Generate the Conference Response Report:** 1. **Open and Save the Workbook:** - Open `NP EX 7-3.xlsx` from the `Excel7 > Case` folder. - Save it as `NP_EX_7.STEM` in your instructor's specified location. 2. **Documentation Sheet:** - Enter your name and the current date in the Documentation worksheet. 3. **Insert VLOOKUP for Workshop Responses:** - In the Survey Results worksheet, use VLOOKUP to translate Q1 answers (a-d) to descriptive text in the Workshops column. 4. **Insert VLOOKUP for Speakers, Meals, etc.:** - Repeat the VLOOKUP process for Q2 through Q4 fields in the Speakers through Meals columns using AutoFill for efficiency. 5. **Insert VLOOKUP for School Type:** - In the School column, use VLOOKUP to identify each attendee's school type (Public, Private, Online, Tutor) based on Q5 responses. 6. **Insert VLOOKUP for Prior Conferences:** - In the Prior Conferences column, use VLOOKUP to categorize the number of previous conferences attended (0, 1, 2, 3+) based on Q6 responses. 7. **Report Worksheet:** - **a. Count “will return” Records:** - In cell B14, use COUNTIF to count records in the Return field equal to “will return.” - **b. Calculate the Difference:** - In cell B15, subtract the count in B14 from the count in B11. - **c. Count by School Type:** - In cells B18 to B21, count the number of attendees from each school type (Public, Private, Online, Tutor) using COUNTIF. - **d. Count by Prior Conferences:** - In cells B24 to B27, count the number of attendees with 0, 1, 2, and 3+ prior conferences using COUNTIF. Each step ensures accurate data translation and aggregation for the conference response report.

Solución de inteligencia artificial de Upstudy

Responder

Solución

Bonus Knowledge

preguntas relacionadas

Latest Computer Technology Questions