[go: up one dir, main page]
Scribd
Upload
26 views3 pages

A06 Instructions

This document provides instructions for an assignment involving Excel lookup functions. It includes 3 exercises: 1. Using lookup functions to fetch specific data points from a dataset about countries, including internet usage, median ages, regions, and more. 2. Classifying countries' internet usage levels as "Too High", "High", "Average", "Low", or "Too Low" based on usage percentages, using 4 different tables with the same data presented differently. 3. Extracting data from the original country dataset based on selectable criteria, and copying the function to automatically update all results as the criteria change. Students are to complete the exercises in an provided Excel file and submit it for grading.

Uploaded by

yaboiglyph
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
26 views3 pages

A06 Instructions

This document provides instructions for an assignment involving Excel lookup functions. It includes 3 exercises: 1. Using lookup functions to fetch specific data points from a dataset about countries, including internet usage, median ages, regions, and more. 2. Classifying countries' internet usage levels as "Too High", "High", "Average", "Low", or "Too Low" based on usage percentages, using 4 different tables with the same data presented differently. 3. Extracting data from the original country dataset based on selectable criteria, and copying the function to automatically update all results as the criteria change. Students are to complete the exercises in an provided Excel file and submit it for grading.

Uploaded by

yaboiglyph
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

American University of Beirut

Department of Computer Science


CMPS 209 - Fall 2023
Assignment 6
Sessions of: Oct 9 – Oct 13

Objectives
u Excel Lookup Functions

Instructions
u Download the datafile “Fall23-A06.xlsx” from Moodle and save it in your drive

Exercise One: General


Open sheet “InfoFetch” in your datafile.

Insert the corresponding functions in specified cells to get the following:

1. K7: Internet usage in France


2. K10: Highest median age
3. L10: Internet usage in the country with the highest median age
4. K13: The region and the income type of Austria
5. K16: Is the working population larger than the non-working population in Egypt?
6. K19: Which Asian country with upper-middle income is displayed in the table?

7. The cell K23 contains a drop-down list of all the countries displayed in the table. Insert in cells L23, M23,
N23, and O23 respectively the needed function to get: the median age, the region, the income type, and
the internet usage, for the country selected by the user (from the list in the cell K23):
Exercise Two: Internet Use Level

Open sheet “Internet Use Level”.

The internet usage is considered “Too High”, “High”, “Average”, “Low”, or “Too Low” level as follows:

• [0-40%[ àToo Low


• [40% - 55%[ àLow
• [55% - 70%[ àAverage
• [70% - 85%[ à High
• >=85% à Too High
Four different tables are included in the worksheet. They all enclose the same information, but with different
presentation.

8. Insert in cells F4, G4, H4, and I4 the corresponding functions to get the level of Internet usage, using
respectively table 1, table 2, table 3, and table 4 (that is, in column F you have to use table 1; in column G
you have to use table 2 and so on). Copy the same functions to fill the columns.
Hint: Fill first the missing values in the columns/rows that are kept empty in the tables.

A partial snapshot is shown below:

Page 2 of 3
Exercise Three: Data Extraction

Open sheet: “Data” in the same workbook.

In additional to the previous dataset, this sheet includes a smaller table that you have to fill using only one function.
Notice that, cells J5 and K5 contain drop down menu storing all the titles of the previous dataset. Thus, the values there
can change.

Insert in cell J6, the appropriate function to extract from the original table, the needed information about Cyprus. The
needed information is based on what we select in cells J5 and K5. Copy the same function to fill the whole table. Make
sure that whenever you choose other values in J5 and K5, all the results should be updated automatically.

Submission: Save your file and upload your file to Moodle.

Enjoy your work!!

Page 3 of 3

You might also like