Download the file and rename it as CLASS SEAT NO NAME Reference Practice (eg

Basic in Excel
1. Excel Cell
2. Excel Range
3. Excel Worksheet
4. Excel Workbook

Basic calculation
In order to start aincalculation
Excel in a cell, we will type
the = sign (equals), and then type our calculation.

These are the basic operators which can be used:

+ Add
– Substract
/ Divide
* (Asterisk) Multiply
AT NO NAME Reference Practice (eg: J315 01 TAN CHIA WEI Reference Practice)

Let's Practice!
Calculate using the = sign (equals)
Write your answer here!
19+7 equals to?
18-5 equals to?
5 X 8 equals to?
144 divided by 12 equals to?
Simple Formula
Calculate by referencing Cell 1 & Cell 2 Complex formula

Cell 1 Cell 2 29+7 equals to? 2+3*4-1 equals to?

29 7 36 5+3*7-2 equals to?
150-8*7 equals to?
Cell 1 Cell 2 34-5 equals to? 220+6*9 equals to?
34 5

Cell 1 Cell 2 7 X 8 equals to?

7 8

Cell 1 Cell 2 144 divided by 12 equals to?

121 11
Using Parentheses-先乘除后加减

#ERROR! 2 +(3*4)-1
 Calculate the Cost of Sales Tax for a catering invoice using cell reference and parentheses

tax rate: 0.075

Cost of Sales Tax

Menu Item Price Quantity (Price*Quantity)*tax rate

Beef $2.99 20
Shrimp $5.99 16
Chicken $4.99 20
Vegetables $2.99 13
parentheses
 Absolute Cell Reference
do not want a cell reference to change when filling cells.
keep a row and/or column constant.

An absolute reference is designated in a formula by the addition of a dollar sign ($) before the column and row.

Mixed Reference with F4 key

$A$2 The row and column do not change when copied
A$2 The row does not change when copied
$A2 The column does not change when copied

Sales Tax Rate:

Menu Item Price Quantity Sales Tax (Price*Quantity)*Sales Tax Rate
Beef Picadillo 4.99 15
Shrimp 3.99 20
Black bean 2.49 10
Chicken Tinga 3.99 10
Vegetables 2.99 20
Carnitas 2.89 30
Quenso Blanco 2.99 15
Apple Cinnamon 3.99 40
Horchata 2.99 35
Lemonate 2.99 15
Tamarindo 2.99 10
Total (Use sum)
ore the column and row.

7.50%
Total (price*quantity)+Sales tax
 Relative Cell Reference
To create and copy a formula using relative references:

Menu Item Price Quantity Total

Beef Picadillo 4.99 15
Shrimp 3.99 20
Black bean 2.49 10
Chicken Tinga 3.99 10
Vegetables 2.99 20
Carnitas 2.89 30
Quenso Blanco 2.99 15
Apple Cinnamon 3.99 40
Horchata 2.99 35
Lemonate 2.99 15
Tamarindo 2.99 10
Total
By using formula, reference cells across “Relative worksheet”

Menu Item Total

Beef Picadillo
Shrimp
Black bean
Chicken Tinga
Vegetables
Carnitas
Quenso Blanco
Apple Cinnamon
Horchata
Lemonate
Tamarindo
use formula ='worksheet name'!cell reference
There are two types of cell references: relative and absolute.
Relative and absolute references behave differently when copied and filled to other
cells.

Absolute references, on the other hand, remain constant no matter where they are
copied.
Relative references change when a formula is copied to another cell.
By default, all cell references are relative references.

comission rate 0.88

Relative reference Absolute reference
price quantity Total Sales Commision
12 1 $ 10,185.00
36 2 $ 11,780.00
27 3 $ 15,023.00
20 4 $ 19,433.00
40 5 $ 12,775.00
42 6 $ 17,859.00
56 7 $ 11,222.00
24 8 $ 10,959.00
54 9 $ 15,153.00
60 10 $ 10,905.00
33 11 $ 16,592.00
36 12 $ 15,065.00
 Complete the multiplication table by using mixed reference

1 2 3 4 5 6 7 8 9 10 11 12
1
2
3
4
5
6
7
8
9
10
11
12
 Let's Practice!

SUM Function MAX Function MIN Function AVERAGE Function

Name No. of Apples Name No. of Apples Name No. of Apples Name No. of Apples
John 30 John 30 John 30 John 30
Steve 50 Steve 50 Steve 50 Steve 50
Laura 60 Laura 60 Laura 60 Laura 60
Diana 20 Diana 20 Diana 20 Diana 20
Sam 80 Sam 80 Sam 80 Sam 80
Lily 40 Lily 40 Lily 40 Lily 40
SUM MAX MN AVERAGE

SUM Function MAX Function MIN Function AVERAGE Function

Name No. of Banana Name No. of Banana Name No. of Banana Name No. of Banana
John 100 John 100 John 100 John 100
Steve 55 Steve 55 Steve 55 Steve 55
Laura 60 Laura 60 Laura 60 Laura 60
Diana 75 Diana 75 Diana 75 Diana 75
Sam 80 Sam 80 Sam 80 Sam 80
Lily 95 Lily 95 Lily 95 Lily 95
SUM MAX MN AVERAGE

SUM Function MAX Function MIN Function AVERAGE Function

Name No. of Coconut Name No. of Coconut Name No. of Coconut Name No. of Coconut
John 1200 John 1200 John 1200 John 1200
Steve 200 Steve 200 Steve 200 Steve 200
Laura 180 Laura 180 Laura 180 Laura 180
Diana 110 Diana 110 Diana 110 Diana 110
Sam 100 Sam 100 Sam 100 Sam 100
Lily 1180 Lily 1180 Lily 1180 Lily 1180
SUM MAX MN AVERAGE

SUM Function
No. of MAX Function
No. of MIN Function
No. of AVERAGE Function
No. of
Name Dragonfruit Name Dragonfruit Name Dragonfruit Name Dragonfruit
John 190 John 190 John 190 John 190
Steve 250 Steve 250 Steve 250 Steve 250
Laura 175 Laura 175 Laura 175 Laura 175
Diana 225 Diana 225 Diana 225 Diana 225
Sam 240 Sam 240 Sam 240 Sam 240
Lily 180 Lily 180 Lily 180 Lily 180
SUM MAX MN AVERAGE