Sample:

Department No of Staff No of Female No of Male Manager

Account 20 5 15 Mr Tan
Marketing 10 3 7 Ms Teo
QS 15 5 10 Mr Chin
Site 20 1 19 Mr Danny

Every formula starts with =

Equations started with + will convert to =+
Equations started with - will convert to =-

Drag Link Cell, Track Link Data ctrl+]

Formula Results Demo Demo
=+B3 Account Account 20
=+B4 Marketing Marketing 10
=+B5 QS QS 15
=+B6 Site Site 20

Drag Cell with alternate Figure

Example: Demo
Account Account Account Account Account
Marketing Marketing Marketing Marketing Marketing

Account Account
Marketing Marketing

Account Account
Marketing Marketing
Cell Linked Automatically

Trace Cell by : Ctrl+[

Drag Cell with Number Demo
C1 D1 A 1 C1
C2 D2 A 2 Drag Cell includes formul
C3 D3 A 3
C4 D4 A 4
C5 D5 A 5

Fixed Specifc Cell

=$B$3
Account Account Account Account
Account Account Account Account
Account Account Account Account
Account Account Account Account
Account Account Account Account

Fixed Column
 =$B3
Account Account Account Account
Marketing Marketing Marketing Marketing
QS QS QS QS
Site Site Site Site

Fixed Row
=B$3
Account 20 5 15
Account 20 5 15
Account 20 5 15
Account 20 5 15
 Account
Marketing

Cell Linked Automatically when drag

Trace Cell by : Ctrl+[

D1 A 1
Drag Cell includes formula

Drag Cell recognised Font style

 IF If([Scenario to test],[What to do if TRUE],[What to do if FALSE])

IF function, to determine the scenario's value if true or value if false

1) If(logical test,value if true,value if false)

Material Market Price Subcon 1

Concrete 15.00 13.00
Steel 10.00 7.00
Sand 3.00 5.00

2) AND ( more than one Logical Test)

Material Market Price Code 1.00 0

Concrete 1.00 1 Cement Sand
Steel 1.00 3 Y10 Y12
Sand 3.00 3 Coarse Fine

3) Or ( Either one Logical Test)

Material Market Price Code 1.00 0

Concrete 1.00 1 Cement Sand
Steel 1.00 3 Y10 Y12
Sand 3.00 3 Coarse Fine

4) Using if in "Value if False" to search the answer you want

Range Low Medium High Very High

Concrete 1 2 3 4
Concrete 1 2 3 4
 *Value in text form must start and end with Quotation Mark " "

Demo If Formula
Within Budget =IF(D9>C9,"Over Budget","Within Budget")
Within Budget =IF(D10>C10,"Over Budget","Within Budget")
Over Budget =IF(D11>C11,"Over Budget","Within Budget")

Logical Test 1 Logical Test 2 Logical Test Demo If (and) Formula

Concrete 1.00 1 Cement =IF(AND(H16=B16,I16=C16,J16=D16
Steel 5.00 5 Y12 =IF(AND(H17=B17,I17=C17,J17=D17
Sand 2.00 3 Fine =IF(AND(H18=B18,I18=C18,J18=D18

Logical Test 1 Logical Test 2 Logical Test Demo If (and) Formula

Concrete 1.00 1 Cement =IF(OR(H23=B23,I23=C23,J23=D23)
Steel 1.00 5 Y10 =IF(OR(H24=B24,I24=C24,J24=D24)
Wood 2.00 2 Fine =IF(OR(H25=B25,I25=C25,J25=D25)

Logical Test 1 Demo If, If Formula

2 Medium =IF(H40=C40,C$39,IF(H40=D40,D$39,IF(H40=E40,E$39,IF(H4
4 Very High =IF(H41=C41,C$39,IF(H41=D41,D$39,IF(H41=E41,E$39,IF(H4
B16,I16=C16,J16=D16),E16,F16)
B17,I17=C17,J17=D17),E17,F17)
B18,I18=C18,J18=D18),E18,F18)

23,I23=C23,J23=D23),E23,F23)
24,I24=C24,J24=D24),E24,F24)
25,I25=C25,J25=D25),E25,F25)

(H40=E40,E$39,IF(H40=F40,F$39,""))))
(H41=E41,E$39,IF(H41=F41,F$39,""))))
 NAME Package Item Monthly Data
Charges

Auntie CO ADV (012)3146321 - -

David Ong VS50 (012)3758221 50.00 -
Muhammad Nazri VS50 (012)3887686 8.06 -
Grd Flr Sms MOP 98 (012)4060221 98.00 -
Griv MOP 98 (012)4061221 98.00 -
Azri Izham VS50 (012)4070186 8.06 -
Khairul VS50 (012)4103221 50.00 -
Chiang Woi Beng VS50 (012)4105221 50.00 -
CPY _CHChoo CO ADV (012)4109221 - -
Chong PY MOP 98 (012)4123221 98.00 -
Chuah Ch VP80 (012)4184221 80.00 68.00

*Table array 1st column must be lookupvalue data column

VLOOKUP
Lookup Value in a table (Column) *lookupvalue must be unique value, cannot duplicate
=VLOOKUP(lookupvalue,table array,col index no, range lookup)
1) NAME Package Item Monthly CharData
Muhammad Nazri VS50 (012)3887686
Grd Flr Sms MOP 98 (012)4060221
Griv MOP 98 (012)4061221

2) Match in vlookup
=MATCH(lookupvalue,lookuparray, match type)
NAME Package Item Monthly CharData
Muhammad Nazri VS50 (012)3887686
Grd Flr Sms MOP 98 (012)4060221
Griv MOP 98 (012)4061221

HLOOKUP
3) =HLOOKUP(lookupvalue,table array,row index no, range lookup)

Row NAME Package Item Monthly CharData

2 Auntie CO ADV (012)3146321 0 0
3 David Ong CO ADV (012)3146321 0 0
4
5

4) Match in hlookup
NAME Package Item Monthly CharData
Griv MOP 98 (012)4061221 98 0
Azri Izham VS50 (012)4070186 8.06 0
Khairul
Chiang Woi Beng
CPY _CHChoo
 Value Added Services [Link] Other Charges Discounts & Total Calls Data Business Usage
Charges Rebates Voice VPN Charges

5.00 0.45 - (5.00) 0.45 0.45 - - 0.45

5.00 30.20 - (5.00) 80.20 29.65 0.55 - 30.20
0.81 1.00 10.00 (0.81) 19.06 - - 1.00 1.00
5.00 1.30 - (5.00) 99.30 - 0.30 1.00 1.30
5.00 0.08 - (5.00) 98.08 0.08 - - 0.08
0.81 - 10.00 (0.81) 18.06 - - - -
5.00 8.45 - (5.00) 58.45 8.35 0.10 - 8.45
5.00 0.20 - (5.00) 50.20 - 0.20 - 0.20
5.00 10.93 - (5.00) 10.93 10.93 - - 10.93
5.00 3.50 - (5.00) 101.50 - 3.50 - 3.50
5.00 204.62 76.00 (8.38) 425.24 183.02 21.60 - 204.62

okupvalue data column

Countifs
, cannot duplicate =COUNTIFS(criteria range, criteria)
Package No
CO ADV 2
MOP 98
VS50

Sumifs
=sumifs(sum range,criteria range,criteria)
Package Monthly Charges
CO ADV 0
MOP 98
VS50

Find & Replace

To replace
Ctrl + F > Replace

Column to Text Function

Separate Text in one cell to few cell
Data Tab> Text to Column>
VC-1
VC-2
VC-3
DM-1
DM-2

*Use & to create unique code for Lookup Function

 *Use & to create unique code for Lookup Function
&
Combine text *Create space by " "
=A1&" "&A2
2017 Lee 2017 Lee *Create - by "-"
2018 John
2016 Danny

EOMONTH
Last day of the Month
=eomonth(reference cell,month)
25-Jun-16 30-Jun-16
22-Jun-16
3-Jan-16

1st day of the Month

=eomonth(reference cell,-1)+1 -1 in "month" to get a month before last date,
25-Jun-16 1-Jun-16 and plus 1 to get the 1st day of Month
22-Jun-16
3-Jan-16
 Amount Total Calls Loss Call Over
deducted Limit
from
available
talktime**

(5.00) (4.55) 0.00 (4.55)

(5.00) 25.20 20.35 (124.80)
(0.81) 0.19 8.06 (99.81)
(5.00) (3.70) 98.00 (203.70)
(5.00) (4.92) 97.92 (84.92)
(0.81) (0.81) 8.06 (0.81)
(5.00) 3.45 41.65 (146.55)
(5.00) (4.80) 50.00 (154.80)
(5.00) 5.93 (10.93) 5.93
(5.00) (1.50) 98.00 (1.50)
(8.38) 196.24 (103.02) 196.24

de for Lookup Function

de for Lookup Function

h before last date,

of Month