Example of VLOOKUP with Approximate Match
This example uses the approximate match feature of VLookup() to generate letter grades for
examination marks. If an exact match for the score is not found, the letter grade of the highest score
which is less than

A Weight Conversion Program With Error Handling
This example illustrates a simple weight conversion program using an Excel formula. The formula has
used ISERROR to handle incorrect input. It will display an error message in that case.
Please enter your we

Some Commonly Used Logical Functions
This example shows 3 commonly used logical functions in cell formulas: AND, OR, NOT. It helps you make the decision "Should I marry him/her?". It considers the
important things that help you make the decision whether t

Advanced Example of Conditional Formatting - Highlighting Scores of Failed Subjects
This example shows the highlighting of scores of failed subjects using conditional formatting. In this example, the passing score is 50. The font color of failed
scores wi

English to Chinese Discount Conversion, Using Chinese Numbers
This example uses a simple Excel formula to convert an English sign showing
a discount into another sign showing the same message in Chinese. This example only works correctly
when the source n

Radar Chart - Hong Kong Monthly Average Temperature
A radar chart is used to display changes in values relative to a center point. In this example, a radar chart is used to show the
monthly average temperature in Hong Kong for the 2008 to 2010 period.
Mon

Peoples' Confidence in HK's Future for the 1994 to 2011 Period
This example shows the result of sorting the data in a descending chronological order.
Confidence Level
Confident
Hard to Say
Not Confident
Source: http:/hkupop.hku.hk/
2011
62.8%
5.5%
31.7%
2

Comparison of Hot Beverage Prices
This example shows the use of ISNA when using VLOOKUP. When the price of a
particular beverage cannot be found, a '-' is displayed rather than the '#N/A'.
Hot Beverage
Starbucks
tall
grande
Pacific Coffee
venti
tall
grand

Simple Example of Conditional Formatting Type 3
This is a simple example for the conditional formatting type 3, "Format only top or bottom
ranked values". We use this to change the text into red if the cell contains values in the top 3.
Value
64
50
30
88

Line Chart - Number of Sent and Received SMS in Hong Kong
A line chart is used to display trends over time or categories. In this example, a line chart is used to show the number of sent and received SMS in
Hong Kong for the 2002 to 2009 period.
Year
2002

Example of String Concatenation
This example shows how to concatenate two strings in cell formulas by using & and CONCATENATE. You can enter two strings in
cells B5 and B6. The result is shown in cell D9 and D10.
Input From User
String A
David
String B
Ro

People's Appraisal of Chief Executive's Job Performance
This example shows the result of sorting the entire data using the 'Average Peformance
Appraisal column' by a custom list (Very Good, Quite Good, Half-half, Quite Bad, Very Bad).
Year of Survey
2005

Some Commonly Used String Handling Functions
These examples show 6 commonly used string handling functions in Excel cell formulas: LEN, UPPER, LOWER, LEFT, RIGHT,
SUBSTITUTE.
Input From User
String A
Dave is the best instructor!
String Old
best
String New

Advanced Example of Conditional Formatting - Highlighting the Top 5 Most Frequently Visited Blogs of TVB Artists
This example highlights the top 5 most frequently visited blogs of TVB Artists. We use the conditional formatting rule type 3, "Format only to

Stock Chart - Hang Seng Index in 2010
A stock chart is most often used to illustrate the fluctuation of stock prices. In this example, a stock chart is used to
show the behaviour of the Hang Seng Index in 2010.
Month
January
February
March
April
May
June

Examples of Relational Operators
These examples show the most common relational operators in cell formulas. The users can enter two numbers
in cells B5 and B6. The results of several comparisons are shown in cells D9 to D14.
Input From User
Number A
3
Num

Example of HLookup Function - Converting Exam Grades into Chinese
This example uses HLookup() to convert examination grades into Chinese. HLookup() is very similar to VLOOKUP but
has only one difference which searches the value horizontally. Hlookup() is

Examples of Arithmetic Operators
These examples show the basic arithmetic operators in cell formulas. The users can enter two numbers in cells B5 and B6.
The results of several calculations are shown in cells D9 to D13.
Input From User
Number A
3
Number B

Single Bar Chart - Worldwide Mobile Phone Sales to End Users in 2010
A bar chart is used to illustrate comparisons among individual items. In this example, a bar chart is used to show
worldwide mobile phone sales to end users in 2010.
Units
(Thousands)
11

The Titanic's Death Status
This example shows the result of sorting the Titanic's death data by the '% Death' column in descending order.
People on the Ship
Number of Deaths
Percentage of Death
Economic Status
Unknown
Third Class
Second Class
First Class

Examples of Several Cell Formula Errors
This example illustrates several types of cell formula error.
Example of #N/A
This example illustrates the #N/A error, caused when a look up cannot find the item it is searching for.
Name
Dog
Puppy
Tiger
Cat
Kitten

Some Commonly Used Number Functions
This example uses the following 7 cell functions in the cell formulas: SUM, AVERAGE, STDEV, MAX, MIN, RANK, COUNTIF. (In this course, each of the
assignments is worth 14% and the final exam is worth 44%. This course is

Advanced Example of Conditional Formatting - Indicating the Level of Capital Punishment in Each Country
This example indicates the level of capital punishment in each country. The background color will be changed according to how many people are executed.

Example of VLookup Function - Converting Exam Grades into Chinese
This example uses VLookup() to convert examination grades into Chinese. Vlookup() is used to find an exact or
approximate value inside a range using the key you have given and returns the c

The IF ELSE Structure
This example shows the extended use of the IF cell function. In this example
if the tested condition fails we return something specific, instead of simply
returning FALSE.
Money in Pocket
Cost of Chocolate
90
10
I have enough money t

Single Column Chart - Box Office Income of Movies About China
A column chart is used to show data changes over a period of time or to illustrate comparisons among items. In this example, a
column chart is used to show the box office income of famous movie

Example of HLookup Function - Finding the Chinese Zodiac Animal For Each Year
This example uses HLOOKUP function to find which year belongs to which Chinese Zodiacs animal. Chinese
Zodiac (Shngxio, ) is a scheme that relates each year to an animal and its

A Weight Conversion Program Without Error Handling
This example illustrates a simple weight conversion program using an Excel formula. The formula does not
have error handling and therefore will show #VALUE! when the input is not a valid number.
Please en

3D Column Chart - Hong Kong Resident Educational Attainment
A 3D column chart is used to compare data points along two axes. In this example, a 3D column chart is used to show the Hong Kong Resident
Population aged 15 and over by quinquennial age group an

Simple Example of Conditional Formatting Type 2
This is a simple example for the conditional formatting type 2, "Format only cells that contain". We use
this to change the text into red if the cell value <50.
Value
64
50
30
88
99
100

2D Clustered Column Chart - Research Rankings for The Hong Kong University of Science and Technology
A clustered column chart is used to compare values across categories and time. In this example, a clustered column chart is used to show the research rank

Simple Example of Conditional Formatting Type 4
This is a simple example for the conditional formatting type 4, "Format only values that are
above or below average". We use this to change the text into red if the values are below
average.
Value
64
50
30
8

The Cost of Different Types of Diet
This example shows the use of relative and absolute referencing in Excel, which works both with and without a table. It
calculates the cost of different types of diet.
Type of diet
Traditional meat diet
Meat reduced die

An Unformatted Timetable
This is an example of a timetable. The timetable is not formatted. The cells of the timetable look very boring and unattractive. There are no macros
in this example file.
My Timetable
Monday
0900-0930
0930-1000
1000-1030
1030-1100

The Cost of Different Types of Diet
This example shows the use of a table and structured referencing, i.e. [name of column], in cell formula. It
calculates the cost of different types of diet.
Type of diet
Traditional meat diet
Meat reduced diet
Vegetaria

Example of a Formula that uses Absolute and Relative Cell References
This example shows the use of a formula which uses both relative and absolute cell references. First, the formula in cell G6 was carefully
written. Then the cell was copied and pasted in

Example of a Formula that uses Absolute and Relative Cell References
In this example the formula in cell G6 was written. However, it was not written properly. Then the cell was copied and pasted into the area G6 to J14. Because the formula was poorly desi

COMP1022Q Midterm Review Questions Part 2
Conditional Formatting Question
Q5)
In this question, you need to complete the formula used for conditional formatting. The visual
formatting is already set up. You only need to complete the formula for the rule.

COMP1022Q Midterm Review Questions Part 1
Cell Formula and Cell Formatting
Q1) Here is a screen dump showing our target result. The chart at the bottom is included only to give
you an overview of the data.
Page 1 of 5
To achieve this result, you need to e

COMP1022Q
Introduction to Computing with Excel VBA
Assignment 1
Stock Market Prediction
David Rossiter and Gibson Lam
Lab 1 and Lab 2 Work
In lab 1 and lab 2
you worked on stock
market prediction
At the end of lab 2
you created a chart
similar to the on

COMP1022Q
Introduction to Computing with Excel VBA
Assignment 2
Eliza Your UST Counsellor
David Rossiter and Gibson Lam
Lab 4 Work
In lab 4 you made Eliza, a virtual psychologist, using
VBA programming
In this assignment
you need to extend
the work that

COMP1022Q
Introduction to Computing with Excel VBA
Assignment 3
Music System
David Rossiter, Gibson Lam and Wallace Mak
From the first lecture
Reminder - Assignments
Worth:
The first assignment will involve using
Excel formulas only (no VBA)
The second

Doughnut Chart - World Internet Users in 2000 and 2010
Like a pie chart, a doughnut chart is used to show the relationship of parts to a whole; however, it can contain more than one
data series. In this example, a doughnut chart is used to show the popula

Pie Chart - HKUST Graduates Employment Situation in 2009
A pie chart is used to show the size of items that make up a data series, proportional to the sum of the items. In this example, a pie
chart is used to show the HKUST graduates employment situation

Advanced Example of Conditional Formatting - Highlighting the Mr. Hong Kongs that Are Taller Than Average
This example highlights the Mr. Hong Kongs in 2011 which are taller than average. The height values are changed into red if the cell contains values

Line Chart - Hong Kong Gas Consumption in 2010
A line chart is used to display trends over time or categories. In this example, a line chart is used to show the gas consumption in 2010. (1
terajoule = 1012 joules)
Month
January
February
March
April
May
Ju

Simple Example of Conditional Formatting Type 5
This is a simple example for the conditional formatting type 5, "Format only unique or
duplicate values". We use this to change the text into red if the cell contains a unique value.
Value
64
50
99
88
99
100

Scatter Chart - Relationship between Broadband and GDP per Capita
A scatter chart is commonly used for displaying and comparing numeric values, such as scientific, statistical and engineering data. A
trendline is usually added to depict trends in the data

2D Bubble Chart - World Population in 2009
A bubble chart can be used instead of a scatter chart if your data has three data series. In this example, a bubble chart is used to show the population of
individual countries in 2009. Larger circles indicate co

Simple Example of Conditional Formatting Type 6
This is a simple example for the conditional formatting type 6, "Use a formula to determine
which cells to format". We use this to change the number into red if the cell value <60.
Value
64
50
30
88
99
100

Simple Example of Conditional Formatting Type 6 - Highlighting Scores of Failed Subjects
This example shows the highlighting of scores of failed subjects using conditional formatting. In this example, the passing score is 50. The font color of
failed scor

3D Surface Chart - Plotting an Equation
A surface chart is used to show a three dimensional surface that connects a set of data points. In this example, a 3D surface chart is used to show the distribution of
equation z = sin(x)*cos(y).
x
-3.00
y 2.00
2.15

Advanced Example of Conditional Formatting - Highlighting Matching Lottery Numbers
This example shows how to highlight the matching lottery numbers using conditional formatting. Two conditional formatting rules have been added. The first rule is to
highli

Stack Area Chart - Hong Kong Electricity Consumption
A stack area chart is used to display the trend of the contribution of each value over time or categories. In this example, a stack area chart is used to show the
electricity consumption in 2010. (1 ter

Stack Column Chart - Hong Kong Number of Deaths by Leading Causes of Death
A stack column chart is used to show the relationship of individual items to the whole, comparing the contribution of each value to a total across categories. In this example,
a st

100% Stack Area Chart - HKSAR Number of Deaths by Leading Causes of Death
A 100% stack area chart is used to display the trend of percentage contribution from categories over time. In this example, a 100% stack area chart is used to show the
number of dea

Stack Area Chart - Browser Statistics for the 2002 to 2010 Period
A stack area charts are much like line charts, but they are used to display different colors in the areas below the lines. In this example, a stack area chart is used to show the browser st

COMP1022Q Assignment 1
Name: Dennis Ko
Student ID : 20117524
The stock : PCCW Ltd (0008.HK)
You can click on one of the following links to see the analyses of different moving averages:
3-Day Average
10-Day Average
25-Day Average
You can also click on the

COMP1022Q
Introduction to Computing with Excel VBA
Worksheets
David Rossiter
Workbook Structure
The basic structure of an Excel file is:
Workbook
Workbook basically means an Excel file
1 or more worksheets
Each worksheet
has lots of cells
You can think

COMP1022Q
Introduction to Computing with Excel VBA
Using Cell Formula
Functions in VBA
Gibson Lam and David Rossiter
This Presentation
Although we havent seen it before, VBA code can
use most of the cell formula functions
Cell Formulas
VBA Code
Cell form

COMP1022Q
Introduction to Computing with Excel VBA
Sorting in Excel
David Rossiter and Gibson Lam
Sorting in Excel
Sorting is one of the most commonly used features
of Excel
For example, if you want to know the top 5
students with the highest GPA in you

COMP1022Q
Introduction to Computing with Excel VBA
More on Variables
Gibson Lam
This Presentation
This presentation discusses two different topics
related to the use of variables:
Variable Scope
The Variant Data Type
For variable scope we will
focus o

COMP1022Q
Introduction to Computing with Excel VBA
Working with
the Currently Selected Cells
David Rossiter, Oz Lam and Gibson Lam
This Presentation
In this presentation, we look at the use of the
Selection object to get information from
the currently se

COMP1022Q
Introduction to Computing with Excel VBA
Useful Ways to
Handle Spreadsheets
David Rossiter
This Presentation
You know the basic idea of entering data into
Excel and using formulas to process the data
In this presentation we look at four areas

COMP1022Q
Introduction to Computing with Excel VBA
Custom Functions
Gibson Lam and David Rossiter
This Presentation
This presentation discusses the use of custom functions
Using custom functions is one of the ways to have
interaction between Excel cell

COMP1022Q
Introduction to Computing with Excel VBA
Filtering in Excel
David Rossiter, Gibson Lam and Eddie Chan
What is Filtering?
Filtering helps you see what you want to see, and
hide the rest
For example, you have some candy chips with many
different

COMP1022Q
Introduction to Computing with Excel VBA
Arrays
David Rossiter, Eddie Chan and Oz Lam
This Presentation
In this presentation we will look at:
What is an array?
Lower bound and upper bound
Using Option Base 1
WithEnd With
Two dimensional ar

COMP1022Q
Introduction to Computing with Excel VBA
Sorting and Filtering Using VBA
Gibson Lam and David Rossiter
What You Will Learn
In this presentation we will look at these:
A reminder of
sorting and filtering
(without VBA)
Sorting using VBA
Basic

COMP1022Q
Introduction to Computing with Excel VBA
Objects
Gibson Lam, David Rossiter and Eddie Chan
Overview
Object-Oriented Programming is an advanced
topic in computer programming
In this presentation, we will look at these:
Introduction to Object-Or

COMP1022Q
Introduction to Computing with Excel VBA
Basic GUI Controls
Gibson Lam, David Rossiter and Eddie Chan
What is a GUI?
A graphical user interface
(GUI) is a set of visual
elements that a user can
interact with
An example GUI is shown
on the righ

COMP1022Q
Introduction to Computing with Excel VBA
Advanced Example
of Cell Referencing
David Rossiter
Writing a
Correct Formula
In this example we want to know the
best country to get profit by buying
and then selling lots of iPad 2 tablets
The profit

COMP1022Q
Introduction to Computing with Excel VBA
Introduction to
Spreadsheets
David Rossiter
Microsoft Office
Before we learn about VBA, we have to know about
Microsoft Office, which is the main productivity
software used around the world
The two main

COMP1022Q
Introduction to Computing with Excel VBA
More on Do Loops
Gibson Lam and David Rossiter
This Presentation
Previously, we have looked at while loops and
two kinds of do loops, i.e.
WhileWend
Do WhileLoop
DoLoop While
In this presentation, we

COMP1022Q
Introduction to Computing with Excel VBA
More on For Loops
Gibson Lam and David Rossiter
This Presentation
In this presentation we will look at:
1. Using for loops with a step value
2. Nested for loops
At the end of this
presentation we will
s

COMP1022Q
Introduction to Computing with Excel VBA
Introduction to VBA
David Rossiter
Looking at VBA
At this stage we have learned a lot of things
about using Excel (without thinking about VBA)
Now we will start looking at VBA and how it
can be used wit

COMP1022Q
Introduction to Computing with Excel VBA
Cell Formula Basics
David Rossiter and Oz Lam
In This Presentation
We will look at the following topics in this
presentation:
The basics of cell formulas
Arithmetic operators
Relational operators
Log

COMP1022Q
Introduction to Computing with Excel VBA
Getting Started on VBA
Gibson Lam
Overview
We have learned that VBA code runs when Excel
receives some kind of trigger, called an event
An event can be
a keyboard event associated with a macro, or
an

COMP1022Q
Introduction to Computing with Excel VBA
Making Basic Decisions in VBA
David Rossiter, Eddie Chan and Oz Lam
Using IF in a Cell
Let's review the cell formula IF function we saw before
IF( condition , some code or value )
Excel will test
this

COMP1022Q
Introduction to Computing with Excel VBA
A First Look at Excel VBA
David Rossiter
The VBA Trigger
There has to be some kind of trigger
for VBA code to begin
This signal is called an event
There are lots of different possible events
An exampl

COMP1022Q
Introduction to Computing with Excel VBA
Conditional Formatting
Gibson Lam, David Rossiter and Eddie Chan
This Presentation
In this presentation we will look at conditional
formatting, which is a way to highlight cells based on
some conditions

COMP1022Q
Introduction to Computing with Excel VBA
Different Types of Variable in VBA
David Rossiter and Oz Lam
Variables
We have seen this before
David
You can think of a variable as a box
Name
There are different types of box which
hold different thin

COMP1022Q
Introduction to Computing with Excel VBA
MsgBox and InputBox
David Rossiter and Eddie Chan
What is MsgBox?
MsgBox is a useful command to show a few words
in a small window
For example:
Here is the VBA code which generates the message:
MsgBox

COMP1022Q
Introduction to Computing with Excel VBA
Using For Loops
David Rossiter, Gibson Lam, Eddie Chan
This Presentation
Previously we discussed the use of while loops
and two types of do loops
In this presentation we will introduce for loops
COMP102

COMP1022Q
Introduction to Computing with Excel VBA
Handling Errors
David Rossiter and Eddie Chan
Errors in Excel VBA Programs
It is very difficult to avoid errors while you write and
run a VBA program
If an error happens, VBA will immediately stop
runni

COMP1022Q
Introduction to Computing with Excel VBA
Introduction to Looping
David Rossiter, Gibson Lam, Eddie Chan
What is Looping?
A loop is a set of code which repeats many times
Looping is a very useful feature in programming
languages such as VBA bec

COMP1022Q
Introduction to Computing with Excel VBA
Functions and Subroutines in VBA
David Rossiter, Oz Lam and Gibson Lam
This Presentation
This presentation looks at VBA functions and
subroutines
Both are used to group together VBA code so that
the cod

COMP1022Q
Introduction to Computing with Excel VBA
Handling a Common
Programming Mistake
David Rossiter, Eddie Chan and Oz Lam
VBA Can Automatically Make Variables
Usually, when the VBA engine is running some code
and finds an unknown variable name, it w

COMP1022Q
Introduction to Computing with Excel VBA
Macros and VBA
David Rossiter
This Presentation
In this presentation we are going to look at macros:
How to make a macro
Two types of macro
Creating a macro
Using a shortcut to run a macro
Amending/