QTS 307 LECTURE NOTE

QTS 307: COMPUTER APPLICATION I

FOR B.Sc. QUANTITY SURVEYING

2021/2022 SESSION

DEPARTMENT OF QUANTITY SURVEYING

SCHOOL OF ENVIRONMENTAL TECHNOLOGY
FEDERAL UNIVERSITY OF TECHNOLOGY, MINNA

COURSE SYNOPSIS
COURSE TITLE: Computer Application I

COURSE CODE: QTS 307

CREDIT UNITS: 2

CONTACT HOURS/WEEK: 2 Hours Lecture

2
COURSE OUTLINE

1. Introduction to Spread sheets and its Relevance to Quantity Surveying

Profession and Practical applications of spread sheets.
2. The Microsoft Excel Spreadsheet Programme; Concept of worksheets and
workbooks; Definitions of columns, rows, ranges, cells etc; Addresses for
cells and ranges; Entering and editing texts and numbers; Arithmetic and
logical operators; Entering and copying formulae; Absolute and Relative
Cell referencing; Linking sheets and workbooks; Using functions (SUM,
AVERAGE, MAX, MIN, LARGE, COUNT, DATE, etc.); Conditional functions
(IF, SUMIF, SUMIFS, AVERAGEIF, etc); Nested IF Functions.
3. Demonstrating the use of formulas and functions.

COURSE ASSESSMENT:

75% Attendance qualifies a student to sit for the examination.

Continuous assessment is 40% and is made up of quizzes, tests, and seminars.

The written examination is 60%.

LECTURER:
Muhammad-Jamil ABUBAKAR PhD

3
Introduction

A spreadsheet is a software program used to easily perform mathematical

calculations on statistical data and totalling long columns of numbers or
determining percentages and averages.

And if any of the raw numbers imputed into spreadsheet change – like if you
obtain final figures to substitute for preliminary ones for example – the
spreadsheet will update all previous calculations based on the new numbers.

Spreadsheet can also be used to generate data visualisations like charts to

display statistical information.

Spreadsheets

There is great value in the use of spreadsheets for measuring and estimating in
small to medium sized construction outfits. This is applicable to both building
and civil engineering projects.

Spreadsheets can be used in the preparation and generation of the following

documents:

o Measurements

o Bills of Quantities

o Rate Calculations

o Resource Pricing

o Project Valuations and Final Account

o Work Breakdown Schedule

o Goods and Services Received Report

The most popular spreadsheet software’s includes:

1. Microsoft excel (Web, Windows, Mac, Android, iOS)

2. Google sheets (Web, iOS, Android)

3. LibreOffice Calc (Windows, Mac, Linux, Android)

4. Smartsheet (Web, Android, iOS)

4
 5. Quip (Windows, Mac, Android, iOS)

6. EtherCalc (Web)

7. Airtable (Web, Android, iOS)

Terminologies in Microsoft Excel:

Microsoft Excel is a spreadsheet program. We use it to create reports that need

calculations and charts.

1. An Excel file is called a Workbook.

− Default title is Book1

2. Ribbon broken into Tabs (Home, Insert, Page Layout…)

− Tabs broken into groups (Clipboard, Font, Alignment)

3. Name box (left) and formula bar (right)

− Name box shows address of current cell

− Formula bar shows contents of current cell

4. Columns Headings are Lettered, Rows Headings are Numbered

− Columns of a building, rows of chairs

5. Worksheet navigation buttons, Worksheet tabs

− Sheet1

6. Status bar

− Excel behaves differently depending on the current "mode"

− Ready mode. This means nothing is being entered or edited on the

spreadsheet.

− Enter mode. This mode is when you are doing data entry, just typing
in the contents.

− Edit mode. Edit the contents of the current cell. Double‐click on a cell
with data in it, or click inside

− the formula bar for this mode.

5
 − Point mode. Used when linking to cell addresses within a formula or
from an Excel dialog window.

MICROSOFT EXCEL

Microsoft Excel is a program that provide worksheets comprised of rows and

columns. Textual data can be stored in the workbook similarly to a Microsoft
Word table, but the power of Excel is its ability to perform simple to complicated
mathematical calculations.

Writing Formulas and Expressions

• An Excel worksheet, or spreadsheet, is a two-dimensional grid with

columns and rows. The column names are letters of the alphabet starting
with A, and the rows are numbered chronologically starting with the
number one.

• The cells in the first row are A1, B1, C1, and so on. And the cells in the first
column are A1, A2, A3, and so on. These are called cell names or cell
references.

• We use cell references when creating math formulas or functions. For

example, the formula to add the contents of cells B2 and B3 together is:
=B2+B3.

6
 • Ribbon start button – it is used to access commands i.e., creating new
documents, saving existing work, printing, accessing the options for
customizing Excel, etc.

• Ribbon tabs – the tabs are used to group similar commands together. The
home tab is used for basic commands such as formatting the data to
make it more presentable, sorting and finding specific data within the
spreadsheet.

• Ribbon bar – the bars are used to group similar commands together. As
an example, the Alignment ribbon bar is used to group all the commands
that are used to align data together.

How to enter Data into Cells

• To enter data into a cell, click in the cell and begin typing. What you type
also displays in the Formula Bar.

• When entering dates, Excel defaults to the current year if the year portion
of the date is omitted.

• Cell contents may be edited from the Formula Bar or directly inside a cell.

• To edit from the Formula Bar, select the cell and click inside the Formula
Bar.

• When done typing, either press the Enter key or click inside another cell.

• To edit directly inside a cell, either double click inside the cell, or select the
cell and press the F2 key.

7
 • Each cell has a specific format which tells Excel how to display its contents.

• A cell's format may be different than the cell contents.

• For example, if you enter 8.9521 in a cell formatted to show two decimal
places, Excel will display 8.95 in the worksheet cell.

However, Excel will use the actual value you entered when performing
calculations that involve that cell.

How to Propagate Cell Contents

• There are multiple ways to propagate or fill data from one cell to adjacent
cells. Let's begin with two popular keyboard shortcuts that allow us to fill
down or fill to the right.

• To fill adjacent cells with the contents of the cell above, select the cell with
the data and the cells to be filled and press Ctrl + D (the Ctrl key and the D
key) to fill down.

• To fill adjacent cells with the contents of the cell to the left, select the cell
with the data and cells to be filled and press Ctrl + R (the Ctrl key and the
R key) to fill to the right.

• To propagate in any direction, use the Fill Handle to autofill. Click in a cell
with data to be copied, hover the cursor over the cell's lower right corner
until the cursor changes to a thin plus sign (+) or a dark square, and drag
in any direction.

How to Move and Copy Cell Contents

• To move cell contents, right-click in the selected cell and click Cut; then
right-click in the new location and click Paste. Similarly, to copy cell
contents, right-click in the selected cell and select Copy and paste in the
new cell.

• To copy the contents of a cell range, click in one corner of the range,
hold down the left mouse button, and drag to the opposite corner. Then
highlight the same size cell range in the new location and Paste.

• Alternately, depending on the worksheet design, after copying you may

be able to click in a cell and select "Insert Copied Cells" from the right-
click menu.

8
 • To remove the animated border around the original cell, press the ESC key,
or start typing in a new cell.

How to Add and Delete Rows and Columns

To insert a new row, Right-click on a row number and click Insert. Excel always
inserts the row ABOVE the row that was clicked on.

To delete a row Right-click on the row number and click Delete.

To insert a new column, Right-click on a column letter and click Insert. Excel
always inserts the column to the LEFT of the column that was clicked on.
Press the F4 key to continue inserting additional columns.

To delete a column, Right-click on the column letter and click Delete.

Contiguous rows and columns Contiguous columns or rows can be deleted by

highlighting them before clicking Delete.

Non-contiguous columns or rows can be selected by pressing and holding

the CTRL key.

Addresses for cells and ranges

o A cell reference or cell address is a combination of a column letter and a

row number that identifies a cell on a worksheet.

o For example, A1 refers to the cell at the intersection of column A and row
1; B2 refers to the second cell in column B, and so on.

o When used in a formula, cell references help Excel find the values the
formula should calculate.

o For instance, to pull the value of A1 to another cell, you use this simple
formula:

o =A1

9
 o To add up the values in cells A1 and A2, you use this one:

o =A1+A2

o In Microsoft Excel, a range is a block of two or more cells. A range

reference is represented by the address of the upper left cell and the lower
right cell separated with a colon.

o For example, the range A1:C2 includes 6 cells from A1 through C2.

EXCEL REFERENCE STYLES

There exist two address styles in Excel: A1 and R1C1.

A1 reference style in Excel

• A1 is the default style used most of the time. In this style, columns are
defined by letters and rows by numbers, i.e. A1 designates a cell in column
A, row 1.

R1C1 reference style in Excel

• R1C1 is the style where both rows and columns are identified by numbers,
i.e., R1C1 designates a cell in row 1, column 1.

To switch from the default A1 style to R1C1, click File > Options > Formulas, and
then uncheck the R1C1 reference style box.

10
Creating reference in Excel

To make a cell reference on the same sheet, this is what you need to do:

Click the cell in which you want to enter the formula.

Type the equal sign (=).

Do one of the following:

o Type the reference directly in the cell or in the formula bar, or

o Click the cell you want to refer to.

Type the rest of the formula and press the Enter key to complete it.

For example, to add up the values in cells A1 and A2, you type the equal sign,
click A1, type the plus sign, click A2 and press Enter:

To create a range reference, select a range of cells on the worksheet.

For example, to add up the values in cells A1, A2 and A3, type the equal sign
followed by the name of the SUM function and the opening parenthesis, select
the cells from A1 through A3, type the closing parenthesis, and press Enter:

11
To refer to the whole row or entire column, click the row number or the column
letter, respectively.

ARITHMETIC AND LOGICAL OPERATORS

Basically, there are 4 crude types of operators in Excel, mentioned as below:

1. Arithmetic Operators

2. Logical/Comparison Operators

3. Text Concatenation Operator

4. Reference Operators

Each type consists of a set of specifically meant operators for that group.

Arithmetic operators

Operator Excel Formula Description

+ (Addition Operator) = A1 + B1 It makes the addition of two or more than two

numeric values and gives a numeric value as a
return.
– (Subtraction = A1 – B1 Subtracts two or more than two numeric values
Operator) and gives a numeric value in return

* (Multiplication = A1 * B1 Multiplies one numeric value with the other and

Operator) gives a numeric value in return

/ (Division Operator) = A1 / B2 Divides the numerator (should be numeric) value

with the denominator (should be numeric) value
and gives a numeric value in return.

% (Percentage = A1 % Converts the number present in a cell into a

Operator) percentage (i.e. divides the number by 100).

^ (Exponential = A1 ^ B1 Raises the power of the first numeric value by the

Operator) second numeric value.

12
Precedence of Arithmetic Operators:

Arithmetic operators follow the order precedence. Exponential and Percentage

operators have the highest precedence and then Multiplication and Division
followed by Addition and Subtraction.

This means that, whenever you’ll be having an expression with more than one
arithmetic operator, percentage and exponentials will be evaluated first. After that,
Multiplication and division will be given preference to evaluate; finally, addition
and subtraction will be evaluated.

• Though these operators have precedence over each other, brackets can
precede some specific operators and be evaluated first.

• As illustrated in the figure:

Logical/Comparison Operators

Logical or Comparison Operators are used in excel to logically compare the two
values (numeric or text).

13
These operators compare two values (provided through cells or separately) and
return a logical output, i.e., TRUE or FALSE.

Operator Description Excel Formula

= (Equals To) It compares two values and returns TRUE if =A1 = B1

both values are equal; else returns FALSE.

<> (Not Equals To) It compares two values and returns TRUE if =A1 <> B1
both values are not equal to each other else
returns FALSE.
> (Greater Than) It returns TRUE if the value in cell A1 (First =A1 > B1
Value) is greater than the value in cell B1
(Second Value) else returns FALSE.
< (Less Than) It returns TRUE if the value in cell A1 (First =A1 < B1
Value) is lesser than the value in cell B1
(Second Value); else returns FALSE.

>= (Greater than or It returns TRUE if the value in cell A1 (First =A1 >= B1
equals to) Value) is greater than or equals to the value
in cell B1 (Second Value); else returns
FALSE.
<= (Less than or It returns TRUE if the value in cell A1 (First =A1 <= B1
equals to) Value) is less than or equals to the value in
cell B1 (Second Value); else returns FALSE.
An example for the comparison operators:

Reference Operators

• Excel reference operators are used to refer to the ranges from an excel
spreadsheet in a formula.

• Example of some reference operators are shown in the table:

14
 Operator Description Formula

: (Colon) It’s a range operator. Which = SUM(A2:B6) #Provides all cells as a

gives a range of cells within start range starting from A2 to cell B6.
point and end point reference
cells
, (Comma) Union Operator. This operator = SUM(A2:A6, A2:B3)
combines (as the word union
suggests) two or more than two
references into a single
reference.
(space) Intersection Operator. This = SUM(A2:A6 A2:B3)
operator only gives reference to
those cells which are common
in two range arguments.

Range Operator:

Let’s take an example. Suppose below is the data you have:

In cell C2, we can see the range operator. It takes a range of all cells starting from
A2 to B6 under the SUM function and returns the sum of all 10 values. See the
output in the second table.

15
Union Operator:

• Cell D2 of the excel spreadsheet shows the functioning of the union

operator.

• It takes two ranges as a reference, first from column A ( A2: A6) and the
other as (A2: B3) under the SUM function.

• Finally, it produces sum of these two references and gives a value of 56.

Copy/Paste a Formula

o When you copy a formula, Excel automatically adjusts the cell references
for each new cell the formula is copied to.

o To understand this, execute the following steps.

o 1. Enter the formula shown below into cell A4.

o 2a. Select cell A4, right click, and then click Copy (or press CTRL + c)...

o When you copy a formula, Excel automatically adjusts the cell references
for each new cell the formula is copied to.

16
 o 2b. You can also drag the formula to cell B4.

o Select cell A4, click on the lower right corner of cell A4 and drag it across
to cell B4.

o This is much easier and gives the exact same result!

o Result. The formula in cell B4 references the values in column B.

Insert Function

o Every function has the same structure. For example, SUM(A1:A4).

o The name of this function is SUM.

17
o The part between the brackets (arguments) means we give Excel the
range A1:A4 as input.

o This function adds the values in cells A1, A2, A3 and A4.

o It's not easy to remember which function and which arguments to use for
each task.

• To insert a function, execute the following steps.

• 1. Select a cell.

• 2. Click the Insert Function button.

• The 'Insert Function' dialog box appears.

• Fortunately, the Insert Function feature in Excel helps you with this.

o 3. Search for a function or select a function from a category.

o For example, choose COUNTIF from the Statistical category.

o 4. Click OK.

o The 'Function Arguments' dialog box appears.

o 5. Click in the Range box and select the range A1:C2.

o 6. Click in the Criteria box and type >5.

o 7. Click OK.

18
COUNTIF FUNCTION

o Result. The COUNTIF function counts the number of cells that are
greater than 5.

o Note: instead of using the Insert Function feature, simply type

=COUNTIF(A1:C2,">5").

o When you arrive at: =COUNTIF( instead of typing A1:C2, simply select
the range A1:C2.

19
RELATIVE AND ABSOLUTE CELL REFERENCES

There are two types of cell references: relative and absolute. Relative and
absolute references behave differently when copied and filled to other cells.
Relative references change when a formula is copied to another cell.

Absolute references, on the other hand, remain constant no matter where they
are copied. By default, all cell references are relative references. When copied
across multiple cells, they change based on the relative position of rows and
columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the
formula will become =A2+B2. Relative references are especially convenient
whenever you need to repeat the same calculation across multiple rows or
columns.

Relative References

To create and copy a formula using relative references:

In the following example, we want to create a formula that will multiply each
item's price by the quantity. Instead of creating a new formula for each row, we
can create a single formula in cell D4 and then copy it to the other rows. We'll
use relative references, so the formula calculates the total for each item correctly.

1. Select the cell that will contain the formula. In our example, we'll select cell
D4.

2. Enter the formula to calculate the desired value.

20
• In our example, we'll type =B4*C4.

• Press Enter on your keyboard.

• The formula will be calculated, and the result will be displayed in the cell.

3. Locate the fill handle in the bottom-right corner of the desired cell.

• In our example, we'll locate the fill handle for cell D4.

• Click and drag the fill handle over the cells you want to fill.

• In our example, we'll select cells D5:D13.

21
4. Release the mouse.

o The formula will be copied to the selected cells with relative references,

o displaying the result in each cell.

o You can double-click the filled cells to check their formulas for accuracy.
The relative cell references should be different for each cell, depending on
their rows.

22
23