Customer Book Pen Pencil Ruler Total

Aye Aye Wai 20 20 30 10

Myo Ko Ko 10 15 12 30
Min Min 12 30 25 30

Course Fees Qty Amount Dividend

Basic 17,000 20 20
Advanced Excel 37,000 10 25
Graphic 70,000 5 30

Discount
Book Qty Price(Doz) Amount Net
5% Amount
Full Mark 10 1,200
Arrow 15 1,100
Snow White 16 1,500
Cherry 8 1,360
Toyo 28 2 1,400
Total

January February March April

Income 500,000 800,000 100,000 120,000
Salary 10,000 10,000 15,000 10,000
Room Rent 6,000 7,000 8,000 9,000
Expenses 4,000 5,000 90,000 1,500
Profit

Service Commerical Discount

Sale Tax Tax
Bill Customer
NO Name 10% 5% 3%
1 Mg Khant 1,300
2 Daw Mya 1,400
3 Win Htut 2,500

Course Price Discount Rate Net amount

Basic 40,000 10%
D.T.P 40,000 15%
Graphic 80,000 20% Net Amount = Price -
Advanced Excel 40,000 10%
Web Design 130,000 30%
Autocard 50,000 15%
Google Sketch 50,000 15%
Graphic Advance 130,000 30%
VideiEditing 1,000,000 25%
Web Developed 200,000 40%
 Basic Salary Fund Net Income
300,000 15,000
280,000 10,000
250,000 70,000

Divisor Result
4
5
6

Price
(Pcs) Amount Qty * Price(Doz)
Discount Amount * 5% F4
Net Amount Amount - Discount
Price (Pcs) Price(Doz)/12

Profit = Income - ( Salary+Room Rent+Expenses)

Next
Amount
Amount = Sale * 10% F4

Net Amount = (Sale+S.T+C.T)-Discount

t Amount = Price - ( Price * Discount rate )

 Internet Wep
Basic D.T.P Graphic Advanced Peachtree
Month & Application
Course Course Course Excel 2005
Email Course

JAN 40 3 4 5 2 3 1
FEB 30 2 5 5 1 3 2
MAR 30 4 10 15 3 4 3
APR 20 3 10 20 3 3 2
MAY 25 4 9 10 3 6 8
JUN 28 2 6 8 6 2 2
JUL 30 3 8 10 2 7 5
AUG 35 4 4 15 5 2 3
SEP 40 3 6 18 3 6 6
OCT 55 2 3 10 7 3 7
NOV 70 1 5 14 3 4 5
DEC 80 2 8 20 8 8 6

Description Price Qty Amount % of total

% of total ကိုလိုချင်လျှင်
Book 50 23
Amount/Grand totalF4
Pen 65 15
Box 26 25
Ruler 35 36
Grand
Total
 Total

Click the Conditional Formating >> Select Highlight Cells Rules >>
Greater Than or Less Than

ကိုလိုချင်လျှင်
t/Grand totalF4
 Name Start Date End Date Years Months Days
Ma Nay Zar 20-Jul-81 3-May-10
Ma Aye Thandar 3-May-1986 6-Aug-10

Mg Nyi 30-Nov-79 30-Nov-09

Daw Mya Nyein Htay 26-Jan-84 1-Jul-07
Ma Khin 2-Feb-86 20-Jun-15
U Wai Moe 3-Mar-81 15-Jan-11

Name Start Date End Date Result

Ma Nay Zar 20-Jul-81 3-May-10
Ma Aye Thandar 3-May-1986 6-Aug-10
Mg Nyi 30-Nov-79 30-Nov-09
Daw Mya Nyein Htay 26-Jan-84 1-Jul-07
Ma Khin 2-Feb-86 20-Jun-15
U Wai Moe 3-Mar-81 15-Jan-11
 Year ကိုလိုချင်လျှင်
datedif(S,E,"Y")

Month ကိုလိုချင်လျှင်
datedif(S,E,"YM")

Day ကိုလိုချင်လျှင်
End date-date(year(E),month(E),1)

Result ကိုလိုချင်လျှင်
Day360(S,E)
Start Date 45 Days 3Months 2Years
45 Days = Start dat
02-01-19
3 Months = EDATE
03-05-19
2 years = DATE(YEAR(S)+2,MO
06-07-19

Month Computer Basic Advanced Excel Computer A+

JAN 20 50 17
FEB 16 60 20 Chart
MAR 19 55 32
APR 25 70 15 Insert Tab >> Ch
MAY 20 58 32
JUN 13 32 40
JUL 10 56 21
AUG 15 77 17

YGN Net Education

Subject Student
Computer Basic 300
Advanced Excel 400
Computer A+ 250
Web Developer 150
 45 Days = Start date+45

3 Months = EDATE(S,3)

s = DATE(YEAR(S)+2,MONTH(S),DAY(S))

Chart
Insert Tab >> Charts
NO MEN WOMEN
0-10 110 -110
10-20 200 -200
20-30 290 -290
30-40 380 -380
40-50 470 -470
50-60 560 -560
60-70 650 -650
70-80 740 -740
80-90 830 -830
90-100 920 -920
100- 1010 -1010

Students Myanmar English Maths Chemistry

Mg Aung Myint Tun 60 58 89 77
Ma Aye Aye Khaing 66 57 85 76
Ma Thazin Win 67 50 82 74
Mg Kyaw Kyaw Win 56 60 81 78
Ma Kay Thi 64 52 80 72

Date Name Course Price

1-Jan-19 Ma Khin Basic 16,000
2-Jan-19 Mg Mg DTP 20,000
3-Jan-19 Aye Aye Basic 16,000
4-Jan-19 Thi Thi Basic 16,000
5-Jan-19 Min Min DTP 20,000
6-Jan-19 Yu Yu Graphic 25,000
7-Jan-19 U Hla Graphic 25,000
8-Jan-19 Win Win Basic 16,000
9-Jan-19 Kyi Kyi DTP 20,000
10-Jan-19 Phyu Phyu Web,App 30,000
11-Jan-19 Thuzar Web,App 30,000
12-Jan-19 Myat Thu Basic 16,000
13-Jan-19 Yamin Basic 16,000
14-Jan-19 Sabia DTP 20,000
15-Jan-19 Khaine Khaine Graphic 25,000
16-Jan-19 Aung Aung Web,App 30,000
17-Jan-19 Nyunt Nyunt Basic 16,000
18-Jan-19 Nyein Nyein DTP 20,000
Physics Bio Total
90 77
89 76
97 75
82 69
89 87

1. Home Tab >> Sort & Filter >> Filter

2. Data Tab >> Filter

3. Data Tab >> Advanced Filter
 Amount
PC NO Start Finished Used
400
PC-03 7:00 AM 8:40 AM
PC-06 7:30 AM 10:00 AM
PC-04 7:45 AM 9:45 AM
PC-02 8:00 AM 9:00 AM
PC-08 8:30 AM 10:00 AM
PC-07 8:30 AM 10:30 AM
PC-09 8:35 AM 9:00 AM
PC-03 9:00 AM 10:15 AM

Subject
Computer Advanced Graphic
Month
Basic Excel Design
january 30 40 22
february 20 25 89
march 30 25 22
april 65 78 54
may 50 32 67
june 20 45 89
july 43 88 34
august 65 32 35
september 30 54 22
october 20 25 89
november 25 78 54
december 65 88 44

Total
Count
Maximum student
Count of MAX
Minimum student
Count of MIN
 Used = (finished-start)*24

Amount = used*400F4

Count of MAX ကိုလိုချင်လျှင်

countif(select all computer basic,click Max)
Count of MIN ကိုလိုချင်လျှင်
countif(select all computer basic,click Min)
 NO 1 2 3
Name Ma Khin Ma Chit Ma Nyein
Fri 1 / / /
Sat 2 / / /
Sun 3 off ot off
Mon 4 off off off
Tue 5 / / /
Wed 6 / / /
Thu 7 / / /
Fri 8 A / /
Sat 9 A / /
Sun 10 off off off
Mon 11 / / /
Tue 12 / A /
Wed 13 / / /
Thu 14 / / /
Fri 15 / / /
Sat 16 / / /
Sun 17 off off off
Mon 18 / A /
Tue 19 / / /
Wed 20 / / /
Thu 21 / / A
Fri 22 / / /
Sat 23 / / /
Sun 24 off off ot
Mon 25 / / /
Tue 26 / / /
Wed 27 ot / /
Thu 28 / / /
Fri 29 / / /
Sat 30 / / /
Sun 31 ot off off

NO 1 2 3
Name Ma Khin Ma Chit Ma Nyein

Basic Salary
wages(31)
Leave(A)
OT
Supply 15%
Gross
Fund 5%
Net Salary

wages(31) basic salary/31

Leave(A) countif(select all ma khin,"a")

OT countif(select all ma khin,"ot")

Supply 15% basic salary*15%

Gross basic salary+(wages*ot)+supply-(wages*leave)

Fund 5% basic salary*5%

Net Salary Gross-Fund

 4 5 6 7
Mg Nyi Mg Aung Ma Zar Mg Myo
/ / / /
/ / / /
off off off ot
ot off ot off
/ / / /
/ / / /
/ / / /
/ / / /
/ / / /
off off off off
/ / / /
/ / / /
/ / / /
/ A / /
/ / / /
/ / / /
off off ot ot
/ / A /
/ / / /
/ / / /
/ / / /
/ / / /
/ / / /
ot off off off
/ / / /
/ / / /
/ / / /
/ / / /
/ / / /
/ / / /
off ot ot off

4 5 6 7
Mg Nyi Mg Aung Ma Zar Mg Myo
alary/31

all ma khin,"a")

ll ma khin,"ot")

ary*15%

)+supply-(wages*leave)

lary*5%

-Fund
 Type QTY
Price
Fet Inc (INC)
YY 18 1 50
ABC 15 6 120
ToYo 18 0 65
YY 20 6 80
YY 5 10 120
ToYo 14 0 20
YY 10 20 15
ABC 13 0 20
ABC 8 0 45

Item No Of Times Net Sale

YY
ABC
ToYo

YGN Net - Education

Total
Date Course Fees
Student
05-0ct-19 Basic 2 15,000
05-0ct-19 Basic 1 15,000
05-0ct-19 Operator 2 35,000
16-Oct-19 Graphic 3 35,000
16-Oct-19 Internet 1 7,000
16-Oct-19 Graphic 2 35,000
16-Oct-19 D.T.P 6 40,000
25-Oct-19 D.T.P 5 40,000
25-Oct-19 Internet 5 7,000
25-Oct-19 Basic 4 15,000
25-Oct-19 Operator 4 35,000
25-Oct-19 Basic 2 15,000
25-Oct-19 Graphic 1 35,000
30-Oct-19 Internet 6 7,000
30-Oct-19 Internet 1 7,000
30-Oct-19 Internet 4 7,000
31-0ct-19 D.T.P 3 40,000

Course Income
 Total Total
Student Amount
Basic
Internet
Operator
Graphic
D.T.P
Total
Amount

Amount ကိုလိုချင်လျှင်

(feet*12+inch)*price

No of times ကိုလိုချင်လျှင်
Net sale ကိုလိုချင်လျှင်
countif(select all type,click yy)
sumif(select all type,click yy,select all amou

Amount

Monthly Income

Total Student = sumif(select all course,

click basic, select all total student)
 Total Total
Student Amount Total Student = sumif(select all course,
05-0ct-19 click basic, select all total student)
16-Oct-19
25-Oct-19 Total Student = sumif(select all course,
30-Oct-19 click basic, select all amount)
31-0ct-19
le ကိုလိုချင်လျှင်

ype,click yy,select all amount)

umif(select all course,

ect all total student)
umif(select all course,
ect all total student)

umif(select all course,

select all amount)
 Product Sale Man Unit
sold
Mouse Ma Nay Zar 5
Monitor Ma Aye 6
Mouse Mg Khant 14
Monitor Ma Nay Zar 8
Printer Ma Mya Mya 7
Printer Ma Chit Chit 4
HardDisk Mg Win Htut 3
Keyboard Ma Nyein Nyein 5
Mouse Ma Nay Zar 6
HardDisk Mg Win Htut 9
Memory Stick Mg Win Htut 11
Keyboard Ma Nyein Nyein 24
Mouse Mg Khant 12
Printer Ma Chit Chit 30

ID Name Course Marks

1 Soe Soe Software Engineering 60
2 Win Win Requirement Engineering 90
3 Naing Naing Multivariate Calculus 34
4 Chaw Chaw Software Architecture 96
5 Nyein Nyein Relational DBMS 70
6 Yu Yu PHP Development 34
7 Nyi Nyi Microsoft Dot Net Platform 78
8 Ei Ei HTML & Scripting 87
9 Khant Khant Data Communication 78
10 Ko Htut Computer Network 89
11 Nyi Nyi Software Engineering 67
12 Ei Ei Requriement Engineering 87
13 Khant Khant Multivariate Calculus 56
14 Win Win Software Architecture 45
15 Naing Naing Relatinal DBMS 34
16 Chaw Chaw PHP Development 98
17 Nyi Nyi Microsoft Dot Net Platform 76
18 Ei Ei HTML & Scripting 56
19 Ei Ei Data Communication 99
20 Khant Khant Computer Network 89
21 Ko Htut Requirement Engineering 78
22 Nyi Nyi Multivariate Calculus 67
23 Khant Khant Relational DBMS 87
24 Win Win PHP Development 56
 25 Naing Naing Computer Network 76

Name Total Marks DAVERAGE DMAX

Nyi Nyi

Total Marks = Dsum(select table,4,name and nyi nyi)

Daverage = Daverage(select table,4,name and nyi nyi)

 Ma Nay Zar
Mouse
Monitor

Unit Sold = sumif(select all

unitsold,select all product,click
mouse,select all saleman,click ma
nay zar F4)

Grade Status
C Pass
A Pass
D Fail
A Pass
B Pass
D Fail
B Pass
A Pass
B+ Pass
A Pass
C+ Pass
A Pass
D Fail
D Fail
D Fail
A Pass
B+ Pass
D Fail
A Pass
A Pass
B+ Pass
C+ Pass
A Pass
D Fail
B Pass

DMIN DCOUNT

Total Marks = Dsum ( select all table,column number of

marks,select name and nyi nyi)
Date Department Sale Person Ietm
01-Jan-16 Sanchaung Kyaw Thu Super Coffeemix
01-Jan-16 Hledan Chaw Su Super Lemontea
01-Jan-16 Yankin Thu Lin Super Teamix
01-Jan-16 Sanchaung Min Hein Super Coffeemix
05-Jan-16 Yankin Kyaw Thu Super Lemontea
05-Jan-16 Sanchaung Thu Lin Super Lemontea
05-Jan-16 Hledan Thu Lin Super Teamix
06-Jan-16 Yankin Chaw Su Super Coffeemix
06-Jan-16 Yankin Chaw Su Super Lemontea
06-Jan-16 Hledan Kyaw Thu Super Teamix
06-Jan-16 Sanchaung Thu Lin Super Lemontea
06-Jan-16 Sanchaung Thu Lin Super Coffeemix
06-Jan-16 Hledan Chaw Su Super Coffeemix
06-Jan-16 Yankin Thu Lin Super Lemontea
06-Jan-16 Hledan Kyaw Thu Super Lemontea
06-Jan-16 Yankin Chaw Su Super Teamix
06-Jan-16 Hledan Min Hein Super Teamix
07-Jan-16 Sanchaung Min Hein Super Lemontea
07-Jan-16 Hledan Chaw Su Super Lemontea
07-Jan-16 Yankin Thu Lin Super Teamix
07-Jan-16 Sanchaung Min Hein Super Coffeemix
08-Jan-16 Hledan Min Hein Super Coffeemix
08-Jan-16 Hledan Chaw Su Super Teamix
08-Jan-16 Yankin Kyaw Thu Super Lemontea

Sanchaung Hledan Yankin

01-Jan-16
05-Jan-16
06-Jan-16
07-Jan-16
08-Jan-16

Super Coffee mix Super Tea mix Super Lemon mix

Sanchaung
Hledan
Yankin

Super Coffeemix Super Teamix Super Lemonmix

Kyaw Thu
Chaw Su
Thu Lin
Min Hein

Sale Person Amount Average Maximum

Kyaw Thu

Item Amount Average Maximum

Super Coffeemix

Date Department Average Maximum

1-Jan-16 Sanchaung

Date Sale Person Average Maximum

1-Jan-16 Kyaw Thu
Amount
50,000
50,000
23,500
12,500
25,000
15,000
15,000
55,000
10,000
9,800
5,680
4,200
7,500
36,000
55,000
47,000
56,000
23,500
24,000
5,680
48,000
55,000
50,000
42,000

sumifs(select all amount,select all

date,click 1-Jan,select all dept,click
sanchaungF4)

sumifs(select all amount,select all dept,

click sanchaung,select all item,click
super coffee mixF4)

sumifs(select all amount,select all sale

person, click kyaw thu,select all
item,click super coffee mixF4)
 sumifs(select all amount,select all sale
person, click kyaw thu,select all
item,click super coffee mixF4)

Minimum

Minimum

Dsum(select all the table,column number

Minimum
marks,select date and 1-Jan-16)

Minimum
Gender Height Age Weight
M >10 <=10 <>10
Results

Gender Height Age Weight

Male 18 20 105
Female 12 12 96
dproduct(select table,2,sele
Male 6 14 105
Height & >10)
Female 14 15 75
Male 9 8 65
Female 8 9 45

Code No Subject Fees

001 Computer Basic 50000
002 Advanced Excel 60000 Select Code No,Fees make
003 Computer A+ 100000 Formulas Tab >> Name Ma
004 Networking 150000
005 Graphic Design 70000

Name Code-NO Fees

Maung Maung
Aung Aung
Myat Myat
Hla Hla sumif(aa,click Code No,bb)
Aye Aye
Kyaw Kyaw
San San
Su Su
uct(select table,2,select Dproduct(select all table,column number
Height & >10) marks,select Height & >10)

Select Code No,Fees make define

Formulas Tab >> Name Manager

k Code No,bb)
Student Name Scores Result
Ma Chit 90
Ma Nyein 50
Ma Zar 67
IF(score>50,"pass","fail")
Mg Khant 35
Mg Zaw 43
Ma Aye 73
Ma Yu 81

EMP ANNUAL
NAME BONUS
No SALARY
331980 CHRISTINE 9735 Criteria
339580 SALLY 11363 Salary>=8000
360090 JOHN 15525 Salary<8000
356430 IRVING 5388
347180 EILEEN 7390
363620 VINCENZO 7008
386920 SEAN 5167 IF(salary>=8000,9735
416150 BRUCE 13899 *10%)
365020 ELIZABETH 5538
394150 MASTATOSHI 18770

Date 40% Course Price Net

26-Feb-16 03-Mar-16 Basic 40,000
26-Feb-16 D.T.P 45,000
27-Feb-16 Basic 40,000
27-Feb-16 D.T.P 45,000
27-Feb-16 Advanced Excel 60,000
28-Feb-16 Video Editing 130,000
28-Feb-16 Microsoft Acces 120,000
29-Feb-16 Advanced Excel 60,000
01-Mar-16 Video Editing 130,000
01-Mar-16 Basic 40,000
Graphic
02-Mar-16 Advance 100,000
02-Mar-16 Web Applications 150,000
Graphic
03-Mar-16 Advance 100,000
03-Mar-16 Advanced Excel 60,000
04-Mar-16 Video Editing 130,000
e>50,"pass","fail")

Criteria Bonus
Salary>=8000 15%
Salary<8000 10%

IF(salary>=8000,9735*15%,9735 IF(Annual Salary>=8000,Annual

*10%) Salary*15%,Annual Salary*10%)

if(26-feb<=3-marF4,40%F4,0)

if(26-feb<=3-marF4,40000-
(40000*40%F4),40000)
 Paid
Customer Sale Today Balance Status
Su Su 56000 6000
Mg Mg 15220 15220
Hein Tun 1650 650
Zaw Wai 5900 600
Chit Su 260 260
David 48000 48000
Bo Bo 165000 5000

Inventor Control
Available
Stock
Description Status Reorder
PKT
Qty
Super Coffee Mix 500
Super Tea mix 320
Super Lemon Tea 30
Gold Roast 835
Super One 125
Premier ( 3 in 1 ) 18
premier ( 2 in 1 ) 460
Rich ( Black ) 39
Rich ( Red ) 15

Room Name Date Stay Charge

No Room
6 David 17-Dec-09 2
5 Shwe Ba 17-Dec-09 3
1 Moe Moe 17-Dec-09 2
16 Aung Aung 17-Dec-09 1
15 Cho Khiang 19-Dec-09 2
3 Naing Lin 20-Dec-09 7
2 Zaw Aung 20-Dec-09 3
5 Phyo Naing 21-Dec-09 3
16 Khin Myint 21-Dec-09 1
3 Smith 25-Dec-09 8
2 Hein Soe 25-Dec-09 2
1 Ei Ei Thu 25-Dec-09 4
 Balance = Sale-paid today
Status = if(Balance=0,"cash","credit")

Status = if(Stock<50,"reorder","sale")

QTY = if(Stock<50,1000-stock,0)

Charge
Food Total Room Price
3500 01 to 05 18000
1600 06 to 10 15000
2500 11 to 15 10000
1600 16 to 20 8000
1000
56000
8600
4500 if(RoomNo>=16,stay*8000,if(RoomNo>=11,stay*10000,if
1200 (RoomNo>=6,stay*15000,stay*18000)))
86000
5600 if(RoomNo<=1,stay*18000,if(RoomNo<=6,stay*15000,if(
2600 RoomNo<=11,stay*10000,stay*8000)))
=11,stay*10000,if
18000)))
=6,stay*15000,if(
*8000)))
 Roll
Student Name Myan Eng Math
No
1 Yarzar Tun 65 59 77
2 Aung Moe 56 39 69
3 Phyo Aung 96 98 95
4 Hein Htet 42 56 50
5 Tun Linn 22 10 40
6 Wai Tun 49 58 63
7 Kay Khaing 76 88 79
8 Hala Hla Moe 82 78 86
9 Poe Ei San 25 41 40
10 Ma Cherry 56 13 45
11 Eaindra Chit 96 98 52
12 Thet Moe Aye 95 29 92

Roll
No Student Name Myanmar English Maths
1 Yarzar Tun 65 59 77
2 Aung Moe 56 39 69
3 Phyo Aung 96 98 95
4 Hein Htet 42 56 50
5 Tun Linn 22 10 40
6 Wai Tun 49 58 63
7 Kay Khaing 76 88 79
8 Hala Hla Moe 82 78 86
9 Poe Ei San 25 41 40
10 Ma Cherry 56 13 45
11 Eaindra Chit 96 98 52
12 Thet Moe Aye 95 29 92

Student Table
Student Table
No Name
1 Ei Ei Mon No Name Date
2 Phyo Wai Aung 1 Ei Ei Mon 10:30-12:00
3 Pan Ei Phyu 2 Phyo Wai Aung 10:30-12:00
4 Sanda Aye 3 Pan Ei Phyu 10:30-12:00
5 Nay Zin Oo 4 Sanda Aye 12:00-1:30
6 Win Moe Kyaw 5 Nay Zin Oo 12:00-1:30
7 Aye Aye Moe 6 Win Moe Kyaw 12:00-1:30
8 Khant Nyar Zaw 7 Aye Aye Moe 2:00-3:30
9 Pa Pa Win 8 Khant Nyar Zaw 2:00-3:30
10 Phyu Ei Win 9 Pa Pa Win 3:30-5:00
10 Phyu Ei Wai 3:30-5:00
 Total Average Result Level Criteria

Average
80
75
40
39

if(and(Myan>=40,Eng>=40,M
il")

Credit Pass Fail

if(Avg>=80,"A",if(Avg>=75,"B",if(Avg>

if(Myan>=80,"credit",if(Myan>=40,"

countif(select Myan to Maths ,"

Total day Course Fees Subjects Fees

Mon-Tue Computer Basic 17000
Mon-Tue Advanced Excel 39000
Mon-Tue Graphic Design 40000
Sat-Sun DTP 30000
Sat-Sun AutoCAD 50000
Sat-Sun Computer A+ 20000
Sat-Sun Web Design 150000
Wed-Thu Networking 150000
Wed-Thu
Wed-Thu
 Criteria

Level
A
B
C
D

(Myan>=40,Eng>=40,Math>=40),"Pass","Fa
il")

if(Avg>=75,"B",if(Avg>=40,"C","D")))

credit",if(Myan>=40,"Pass","Fail"))

elect Myan to Maths ,"credit")

Funtion = List
Date Tab >> Data Vaildation >> click
blanks new cells
 Commission Rates
Brian 4%
Dave 5%
VLOOKUP(click builder,select tableF4,2,0)
Doug 6%
Gill 6%
Jones 6%
Jarry 4%
Morgan 4%
Rob 5%

Date Builder Units Average $ Total

06-Jan-08 Dovg 8 389
09-Feb-08 Dave 10 385
15-Mar-08 Dave 3 771
18-Apr-08 Brian 5 313
05-May-08 Larry 10 574
22-May-08 Rob 8 730
25-May-08 Morgan 4 471
15-Aug-08 Jones 1 548
12-Dec-08 Dovg 3 323
10-Apr-08 Dave 5 712
14-Mar-08 Dave 6 432

ID Name Course Marks Grade

1 Soe Soe Software Engineerin 60
2 Win Win Requirement Enginee 90
3 Naing Naing Multivariate Calculus 34
4 Chaw Chaw Software Architectur 96
5 Nyein Nyein Relational DBMS 70
6 Yu Yu PHP Development 34
7 Nyi Nyi Microsoft Dot Net Pl 78
8 Ei Ei HTML & Scripting 87
9 Khant Khant Data Communication 78
10 Ko Htut Computer Network 89
11 Nyi Nyi Software Engineerin 67
12 Ei Ei Requriement Enginee 97
13 Khant Khant Multivariate Calculus 56
14 Win Win Software Architectur 45
15 Naing Naing Relatinal DBMS 34
16 Chaw Chaw PHP Development 98
17 Nyi Nyi Microsoft Dot Net Pl 76
18 Ei Ei HTML & Scripting 56
19 Ei Ei Data Communication 99
20 Khant Khant Computer Network 89
der,select tableF4,2,0) VLOOKUP(Click the builder,select all
tableF4,Column number,0)

Commission $

Prize
Money Marks Grade Prize Money
0 D N/A
60 C 100
65 C+ 250
70 B 700
75 B+ 600
80 A 1000

Grade = Vlookup(click Marks,select tableF4,2)

Prize Money = Vlookup(click Mars,select tableF4,3)
 Code Department Bonus Fund
Dep-1 Admin department 3% 5%
Dep-2 H/R department 7% 2%
Dep-3 Manufacturing Dep 5% 2%
Dep-4 Sales & Marketing Dep 10% 3%
Dep-5 Finance & Account Dep 5% 2%

Employee Name Department Basic Salary Bonus

U Nyi Dep-1 52000
U Khant Dep-3 45000
Daw Chit Dep-3 58000
Thet Htar khin Dep-2 56000
Ma Aye Thandar Dep-4 78000
Ma Nay Zar Dep-1 56000
Ma Mya Nyein Dep-2 80000
Ma Nay Tun Dep-3 59000
Mg Wai Htut Dep-1 65000
U Kyaw Moe Dep-4 72000
Daw Zin Zin Aung Dep-3 59000
Ma Mu Mu Dep-5 72000

Sale <3 >=3

0 5% 7.50%
25000 7% 10%
50000 10% 15%
75000 13% 20%
100000 15% 25%
125000 18% 30%
150000 20% 35%

Customer Years Sales Discount Rates

Black Store 5 150000
Moon Teashop 6 50000
Zinyaw [Link] 4 56000
King & Queen [Link] 2 250000
Khaing Chit Store 1 120000
CTP [Link] 2 15000
Thandar Store 1 10000
Myint Zu [Link] 5 60000
 Fund Net Salary

Bonus = Vlookup(click dep-1,select

tableF4,3,0)
Fund = VLOOKUP(Click dep-1,select
tableF4,4,0)

Net Salary = Salary + ( Salary * Bonus) -

(Salary * Fund)

Net Amount

Discount rates =
if(year>=3,vlookup(15000,selectF4,3),
vlookup(15000,select tableF4,2))
Net Amount = Sales - (Sales*Discount rate)
k dep-1,select
,0)
k dep-1,select
,0)

Salary * Bonus) -
und)

=
selectF4,3),
bleF4,2))
Discount rate)
ID Product Qty Price Total
10277 Apple 12 700 8400
10249 Orange 10 250 2500
10250 Mango 5 400 2000
10251 Pear 9 1000 9000
10252 Grapes 15 1200 18000

ID Product Qty Price Total

10277 Apple 12 700 8400
10249 Orange 10 250 2500
10250 Mango 5 400 2000
10251 Pear 9 1000 9000
10252 Grapes 15 1200 18000

Basic Excel Graphic

17000 37000 70000

Month Subject Student Fees Total

Jan Basic 300
Feb Excel 200
Mar Graphic 250
Apr Basic 250
10250
Product = lookup(click ID,select ID column,select
product column)

Qty = lookup(click ID,select ID column,select Qty

column)

10250

Product = Vlookup(click ID,select table,2)

Qty = Vlookup(click ID,select table,3)

Fees = Hlookup(click basic,select

tableF4,2,0)
No Item Type Stock Price
1 Noodle Food 12 Doz 250Ks
2 Apple Food 150 Box 850Ks
3 Ruler Stationary 124 Doz 260Ks
4 Book Stationary 135 Pkt 120Ks
5 Myanmar Beer Beverage 20 Box 850Ks
6 Apple Rum Beverage 23 Doz 2500Ks
7 Lamp Electric 15 Kpt 450Ks
8 National iron Electric 5 Kpt 6200Ks
9 Juice Beverage 28 Box 280Ks
10 H & A Fan Electric 6 Kpt 3400Ks
11 Good Lam Food 23 Doz 150Ks
12 Correction Pen Stationary 4 Doz 180Ks
13 Tory Fan Electric 23 Kpt 3400Ks

Item Code Name Qty on Hand Unit Price

Amount
Item Code = Left(food)&"-00"&No

Name = left(food)&"-"&item

Qty on Hand = left(12doz,len(12doz)-3)

Unit = Right(12doz,3)

Price = left(250ks,len(250ks)-2)

Amount = concatenate(12,doz)
 Salesperson Region Month Account
Aye Aye East February 74830
Aye Aye East February 90099
Aye Aye West March 74830
Aye Aye West January 82853
Bo Bo West January 72949
Bo Bo West January 90044
Bo Bo West February 82853
Bo Bo West March 72949
Bo Bo South February 55223
Chaw Chaw South January 10354
Chaw Chaw South January 50192
Chaw Chaw South January 27589
Chaw Chaw West February 67275
Nyein Nyein West March 41828
Nyein Nyein West March 78543
Nyein Nyein South January 97446
Zar Zar South January 41400
Zar Zar South February 30749
Zar Zar South February 41400
Zar Zar South February 30974
Ei Ei South March 30974
Ei Ei East January 78532
Po Po East February 78532
February 65532
Po Po East March 78532
Po Po North January 91987
Tun Tun North January 91041
Tun Tun North February 91987
Tun Tun North February 91041
Tun Tun North March 91987
Tun Tun South January 55667
War War South January 54393
War War South January 40028
War War South February 55667
War War South February 54393
War War South March 54393
War War South March 55667
War War South March 27589

Name Country Qty Price

Monitor Indonia 10 15,000
Monitor Indonia 20 15,000
Monitor China 30 20,000
Monitor China 10 20,000
Printer China 20 20,000
Printer Indonia 30 25,000
Printer Indonia 50 25,000
Printer Indonia 60 25,000
System China 40 15,000
System China 50 15,000
System Indonia 60 15,000
System Indonia 40 15,000
 Pivot Table

Insert Tab >> Pivot Table

1. Select all the table
2. Click New or Existing Worksheet
3. Click OK
Delect Pivot Table >> Select all Pivot Table >> Click delect
key

total
150,000
Subtotal

1. Click the Data Tab

 300,000
Subtotal
600,000
200,000 1. Click the Data Tab
400,000
750,000 2. Select the Data
1,250,000
1,500,000 3. Click the Subtotal
600,000
750,000
900,000
600,000
 Shop 1
Subject Month Qty Fees
Computer Basic Jan 200 17,000 3,400,000
Advanced Excel Feb 100 37,000 3,700,000
Graphic Design Mar 150 70,000 10,500,000
AutoCad Apr 250 70,000 17,500,000
Computer A+ May 300 40,000 12,000,000

Shop 2
Subject Month Qty Fees
Computer Basic Jan 300 17,000 5,100,000
Advanced Excel Feb 250 37,000 9,250,000
Graphic Design Mar 210 70,000 14,700,000
AutoCad Apr 320 70,000 22,400,000
Computer A+ May 150 40,000 6,000,000

Total
Subject Month Qty Fees
Computer Basic Jan 500 34000 8500000
Advanced Excel Feb 350 74000 12950000
Graphic Design Mar 360 140000 25200000
AutoCad Apr 570 140000 39900000
Computer A+ May 450 80000 18000000
 Consolidate

Data Tab >> Consolidate

Reference >> Select shop 1 table

>> click add >> select shop 2 >>
click add >> click ok
Bank Balance 100000
Monthly Drag 3500
Interest Rate 13%
No of Period 12

Period Opening Interest Principle Closing

1 100000 3500
2 3500
3 3500
4 3500
5 3500
6 3500
7 3500
8 3500
9 3500
10 3500
11 3500
12 3500

Bank Balance 50000

Interest 12%
No of Period 12

Period Opening Interest Principle Closing

0 50000 5000
1 5000
2 5000
3 5000
4 5000
5 5000
6 5000
7 5000
8 5000
9 5000
10 5000
11 5000
12 5000
 Opening = Closing

Interest = (opening*13%)/12

Closing = (opening+interest)-principle

opening = closing

Interest = (opening*12%)/12

Closing = opening+interest+principle
Loan 280000
Monthly Interest Rate 6%
No Period 12

Month Payment Interest Principal

0
1
2
3
4
5
6
7
8
9
10
11
12

PMT Month Payment ( လစဉ် အပ်ငွေ၊ထုတ်ငွေ )

Rate Month Interest Rate , Per Period ( ဘဏ်တိုးငွေ )
Nper Terms of Value ( အပ်မည့်အကြိမ် အရေအတွက် )
Pv Principle Value ( မူလတန်ဖိုး )
 Balance
PMT(6%/12,12,-280000F4)
280000 Payment
PMT(Rate,Nper,PV,FV,Type)

(Balance*6%)/12
Interest
(Balance*Interest Rate)/12

Principle Payment-Interest

Balance Balance-Principle

ငွေ၊ထုတ်ငွေ )
ဘဏ်တိုးငွေ )
မ် အရေအတွက် )
ဖိုး )