CSCI 112
Fall 2021 - Assignment 8
Submit deadline: 11:30am, 22 November 2021, Monday
Objective:
- Learn to develop a VBA program with (nested) loop statements
References:
Problem Description
In machine learning, an important step of pre-processing the data is
to perform the data normalization. The simplest method of data normalization
is known as min-max scaling or min-max normalization.
To perform the min-max normalization on a sequence of the data, there
are following steps:
- Find the maximum value (max_v) and the minimum value (min_v) among
the data items;
- If max_v and min_v are the same, there is no need to
do the normalization;
Otherwise, for each data item x in the sequence, apply the following
formula:
new_x = (x - min_v) / (max_v - min_v)
And the new_x would be a value between 0 and 1, and is the normalized value.
Your Tasks:
- Download the template file
to your home directory on discovery (your U drive).
- In the input file, it contains one worksheet that stores
some data about a collection of countries. These data
include (from column B to G respectively) life expentancy
in the country, percentage of total income earned by top
10 percent of top income earners, millitary spending as
a percentage of the country's GDP, the average years
a girl spends at school, and the corruption perception index (CPI)
assessed by experts.
- Edit a VBA program that adds a new worksheet named
"Normalized Country Data" to the file, and then
treat each column of data as an independent sequence,
perform the normalization for each and all column,
and store normalized data to the newly added worksheet.
The format of the data should be the same
as the format of the original country data.
(i.e., the first row is a title row, and the first column
contains the country names.)
- If the normalization can't be performed for any column (because
the max value and min value are the same), notify the user
using a message box and simply copy the original data (of that
column) to the new worksheet.
- Save your VBA code and processed data to a (Macro-
enabled) Excel file named as your LastName_A8.xlsm.
Note that there is NO need to design a user interface for this
assignment.
Before submitting your assignment, you need to check at least
the following things:
- Did you use any literal numbers as parameters in your program?
- Did you put "Option Explicit" at the beginning of
your program?
- Did you give meaningful names to your variables?
- Did you indent your source code properly and consistently?
- Did you use repetition statements whenever possible?
- Did you make sure that your VBA program can be used
(with just some minor modification of the code) to normalize
a data collection of about 100 countries (rows) and more than 20
features (columns)?
Submitting:
To submit your assignment, name your solution file your
Lastname_A8.xlsm,
then login to your VIU Learn account, find the CSCI 112 course page,
click on the "Assessment" drop-down menu, click
on the "Assignments" item, then click on the folder named "A8".
Then you can click on the "Add a File" button to browse and upload your
solution file Lastname_A8.xlsm.
If you have difficulties to make the VIU Learn submission work,
please email me to let me know and attach your Excel solution file
with your email.