Part 1_EXCEL (1)

.docx

School

Indiana University, Bloomington *

*We aren’t endorsed by this school

Course

BUSH352

Subject

Information Systems

Date

Feb 20, 2024

Type

docx

Pages

2

Uploaded by emilyguse09

Report
Part 1: Excel Due Date/Time: See Canvas for exact date/time. This is a skill assessment. Penalty for late submissions without approval. Grading will be all or none for each question. Question must be answered perfectly to receive full credit. Download the Excel_Student_File.xlsx from Canvas and save it to your computer. Submit on Canvas: Your completed Excel_Student_File.xlsx only. Part 1 Excel (42 Points) Use Excel_Student_File.xlsx to complete 1. _____Text Manipulation1: Remove the spaces from the text. Use functions. No credit for just editing the text in the shaded column—functions are required. When clicking on the cell (eg., E4) only functions should be visible. No credit to manually editing the text! (7 pts.) 2. _____Data Lookup: Using INDEX & MATCH or XLOOKUP to obtain a value from the table. When the user selects Year and one of the other three metrics (Visitors, Vehicles, or Citations), the number from the table should be reported. Be sure to create drop down lists where appropriate. (7 pts.) 3. _____LOOKUP1: Complete the Political Party Affiliation based on the table provided. Then, count the number of members or affiliations for each party in column K using the appropriate function. (5 pts.) 4. _____LOOKUP2: 1-In the VLOOKUP2 worksheet, obtain the correct Green Car Report Score value from the table in VLOOKUP2 Data worksheet. When there is an error, indicate “Data Not Available.” You may insert columns as appropriate to make this work. (7 pts.) 2-If the score in column C is 18 or less, show the text “ Green .” Greater than 18 have the cell text read “ Inefficient ”. 3-In the last column, if the Report Score is between 20-30, state AVERAGE, below 20 should state LOW, anything above 30 should state HIGH. 5. _____Text Manipulation2: Separate each section of text as shown in the table provided using functions and cell reference to separate each part of the Airline KEY field into its component part. Use any functions you like, but you must use functions to extract all text sections. (8 pts.) 6. _____Calculation App : 1- Create a drop-down list in cell I2 for agent Names. 2 -Total the amount of Sales (in dollars) for an Agent when selected. 3 -Fill in the table with sum of Total Units, sum of Total Sales and count of Number of Sales based on the table of data provided. The figures in the table should change when a new agent is selected in cell I2. (8 pts.) 1
7. _____My Grade: Answer the question on this worksheet on what grade you should have for this activity. 2
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help