................. .. ............. ......... Bagri Computer's ..... Bagri Computer's .............. .. .... .. ... ... , .. .

How to start M.S. EXCEL M.S.EXCEL

M.S. Excel ~ ~ ffi ~ ~ ~ m.)- cf; ~ Microsoft Corporation ~ iPfmT
Click To Start Button ~ ~ (Spread Sheet) ~ t, W Row {tfflffl") aeTT Column ~ ) ;a ~
'7J"<lT
~ ~ t,
~ ¥ll:lmh1)cre ~ Cf>T tt ~ ~ ~ ~ <:~$il,., fffcre~<l'1 1ft t
All Programs (Programs) ~ vfffiT t 1 ~ ~ mmq cl; ~ ~ ;a ~ cf>Tlif ~ ~ er Tf11AT ~
t
'
;a~ UTT wmft I M.S. Excel 2010 'i¥ Total Row ~ "ffiYTT 1048576 cf Column~~
Microsoft Office 16384 tmft t
I M.S. Excel -q Row 1,2,3,4 cf Column A,B,C,D cf; .:rrlf ;a "GIR vfffi I M.S. t
~
Excel 2010 'i¥ Row~ Height 15 cf Column~ Width 8.43 tmft I t
M.S.Excel
Cell - Row (tffi@') <I' Column~) ~~~cm-~ aeTT ~ cf>Tc-t ;a~t
~ mcffi <ITT ~ 'ITTcTT t, ~ Cell ~ vfffiT I ~ -q ~ W <ITT t
How to start M.S. EXCEL with keyboard 3PRT~$ mt, ~w ~~ 9 3reR ~Mt,~ ~w-ir
9
~ ~ 3reR ~ uf@" t
'ITT~ 3reR ~ ###### ~ -q ~ ~ t, .~ ~
Press Keyboard Window Button
~ Entry W cffi" Numerical "ITT "<lT Text "ITT Cell 'i¥ ift ~ ~ I W 'i¥ ~ cm- t
~ ~ "'{~~cf>C'I ~ < I T T ~ Glm cf ~ ~ Cf>T 3le1l:l-iTic ilTlIT ~
'
®Run
t I M.S. Excel -q <iili"i~4i'IR. ~ ~ c2J_ Tf ift ~ I ~ ~ ~ Cell -q Cursor
t
~ t, ~ Active Cell ~ '1fTffi I Active Cell 'i¥ ~ fc);".QT '7J"<lT ~ ~ m Tl
t
Home Page or the M.S. Excel
~ [ ttoo, L ..... ~ ........ . ~
1...:.,ct>·-4Q .,i .,. ,.. •

'
Excel
~.......,, J!II,
n<ft'• · mi., ·,..Kl 1 -"·tr
., • " '
,fl\

~~t~~ ~ i ! R ~ ~ JmJTI, Tf °iftlTTm"GTRITt,

M.S. Excel Tf ~ Cf>T By Default .:rrlf Bookl ifl!TT ~ ~ ~ cfTT ~ .:rrlf
(Extension Name) .XLSX 'ITTcTT t I Excel Tf oPITTfi 1'p:ft ~ cm- ~ ~ "GlRIT t, ~
~ fcITTtT ~ ;a ~ UTT ~ t I ~ ~ JcJ> ~ ~ Ti ~ iffift t . ~
~ ~
,... ....... ,.,,,__
--· '
!.:i:·:..i,-"zl--:-;t 'T[;;;;."--'-':;I
• •I"" ·. < ,, - • -·
1 ru . I.la •r.;· •~o 1:..••·.....
·•!,.,:!,. lll~•t'l.i ,.. ~,... m1R" ~ cft.:r tt ~ ~ ~ t, ~ ~ ~ ~ 3jjq"<lcf>ar rn ' C R ~ ~
~ ~ UTT wmfi t I ~ ~ cm- ~ .:rrlT ~ ct; ~ ~ 31 3laffi cfTT ~
fmm UTT ~ tI
.,
>
PURPOSE OF M.S. EXCEL
.~I
~1 ~ ~ Cf>T ~ ~ ;a cf>nl Company -q ~ cf; fit<RVT cm- ~ ~ 'ITTcTT t,
Company~ ~ ~ cm- ~ fcluJr W ~ lt -
~
-~·1. -~·· - -+----··•'"'
m.r. ffl', 3J:ri<f ~cm-~ ffl ct; fc;n) I
~ : 4>.f,q1~4)' <ti
..., filtlvA ct; llf'5J 1) I
-lll,,
cli©mit-31 ct; q;m Ti ~ :- <l1ft ~ . ~ ~ ~ m ffl ct; fc;n) I
~ cm- ~ ffl ct; fc;n) I ~ q;m ffl ct; ~ I
 , ., --v,
......................................... Bagri Computer's .............. . ................. ........ . ............ .... .... ..................... Bagri Computer's ..................................... ... .
FUNCTIONS OF EXCEL 14. EVEN - ~ Function~ 7fll' ~ ~ ~ "flli 'ffislfT ~ qRoJ' tI
= Even (5.56}
1. ~ • ~ Function ~ 7fll' ~ ~ Absolute Value Return cfR'ffi tI Absolute ~ 6
cmq-q f.Rr ~ ~ ~ Numbercm ~ ~ I 15. ODD• ~ Function~ 7fll' ~~ 3PRfr ~ ~ ~ <lmTT tI
=Abs(-10) =Odd (5.56)
10 7
l. llff · ~ Function~ Number~ Integer Value~ t, ( ~ ~ ~ fflm ) 16. MEDIAN - ~ Function~ 7fll' ~ q;r ~ ~ <lmTT tI
=Int (10.23) = Median (I , 2, 3, 4, 5)
10 3
J. MOD - ~ Function~ 1ft ~ -if ~ ~ q;r 't{JTT ~ {R ul)- ~ q'ifflT t, 17. COUNTIF • ~ Function~ 7fll' Data Range if ~ ~ '1ft Particular Range cf>T Count
-rn ~ ~cm~
~cfR'IDtl t, un- Condition if <ft ~ t I ~
<lmIT : ~ ~ ~ '1ft me.TT ct
= Mod (100, 11) <t>.ft11R4i <tft ~ 3tR ~ ~ l'fUAT ffl 'il ~ mcft t I
I = Countif (Data Range, "Criteria")
4. SORT· ~ Function~ 7fll' ~ q;r ~ ~ cfR'ffi tI = Countif(P,P,P,P,A,P,A,P, " P" )
= Sqrt (81) 6'
9 18. SUMIF • ~ Function ~ Data Range if ~ ~ 'ffislIT <ITT
7fll' vITTf cfR ~ t un-
5. SUM · ~ Function~ 7fll' ~ ~ Total Value cm~ <1ml1' t I Data Range ~ ~ 'it <ft ~ I t
= Sum (8, 9, 5, 5) = SumiftDnta Range, "Criteria")
27 .. Sumif(l5,25,15,25, 15,8, 15, " IS'")
6.AVERAGE-~ Function~ 7fll' ~ ct 3fmc'f cm~ <IRclT t1 60
= Average (5, 15, 10, 12, 8) 19. LEFT - ~ Function ~ 7fll' Data if ~ Left Side cfi m'A tt Character cf>T ~ <lmTT
10
~ Function ~ 7fll' ~ 'it ~ ~ ~ "ffi9TT <ITT ~ <1ml1' tI t ui)- "fmll'T ~ if ~ "GfTcft tI
7. MAX·
= Max (I, 2, 3, 4, S) • Left ("Data", "No. ofCltr racter")
s = Left ("BAGRI COMPUTER CENTRE", "5")
8.MIN• ~ Function ~ 7fll' ~ 'it ~ ~ 'O'ttt "ffi9TT <ITT ~ <1ml1' tI BAGRI
"'Min (I, 2, 3, 4, 5) ~ Right Side cfi m'A tt Character <ITT ~
20. RIGHT· ~ Function ~ lfl) Data if
I
9. COUNT· ~ Function ~ 7fll' ~ 'it ~ Total Number ~ 'ffl9TT <ITT ~ <1ml1' tI <1mll' t ui)- ~ ~ if ~ "GfTcft tI
= Count (4,6, 1,9,7) = Right ("Data", "No. of Character" )
5
= Right ("BAGRI COMPUTER CENTRE", "6")
10. UPPER - ~ Function~ 7fll' Text cm Upper Case ~ 3'ffil) 'it ~ <1m11' t I
CENTRE
= Upper ("Computer")
COMPUTER 21. FACT•~ Function~ 7fll' ~ ~ ~crelR<lc-1 (factorial) "fRs<lT <ITT >l'Gftfu <1m!l' tI
11. LOWER-~ Function ~ 7fll' Text cm Lower Case (mt 3'ffil) 'it ~ t I
= Fact( 5)
= Lower ("COMPUTER") 120
Computer
Jl, PRODUCT-~ Function ~ 7fll' ~ <t;T 'T'fT ~ <1R'ffi tI 22. LEN - ~ Function ~ 7nl Data ct 3leRT ~ ~ ~ '3"f 3leRT ~ cTcC'I' "fRs.QT
=
Product (S, 3) cf>T >l'Gftrn <lmTT t I ~ Function Data li ~ 7nl ffl ~ TfO'AT ~ <lmTT t I
IS
13. EXACT· ~Function~ Text <!ft ~ CIR
err \Rlf ~ True 3l'R False cm '!lGfflo ~ tI "" Len (''Bagri Computer Centre")
=- Exact ("book", "Book") 21
False
 ----------
......................................... Bagri Computer's ........................................ .
--
Practical Session
Exercise 1.
MARUTI UDYOG LTD. Maintains its quarterly sales figures in a worksheet as sheet as shown below:
a. Create the workbook as shown above and save it as MARUTI .XLSX.
b. Calculate the total sales in each quarter and display in respective cells in row 11 of the
worksheet.
c. Save the workbook again.

''t
~
"iii
, B ·
:, .
C
' 111!'1
D
'!l!
f$~·: ;
,
E. ,
~~ ' . F ._ - . G
"' ""'
, ;;,
- is,'
o/-
-~ ~ -.~•. ~ Ii - .

MARUTI UDYOG LTD.

"
1' QUARTERLY SALES OF VEIDCLES FOR THE YEAR 2007.
'
2. ,,
Total
1, Products
QTR.1 QTR.2 QTR.3 QTR.4 Models
Name
3 Sales

4
"r,'

5 M.800 22100 37000 27500 56000 142600

cg '4
6 M. 1000 12000 16000 16950 18000

7 MGYPSY 15650 15620 22000 12500

,,
8 '¥"' MOMINI 35000 29560 37500 36000

9; ' ESTEEM 58000 45630 61000 125000

. ~ <

: 10
·,

Total Qtr.
•., ¾ 142750
11 .· Sales

Total models sales formula c> =SUM(QTR 1:QTR 4)

Example c> =SUM(B5:E5)

Total Quarterly sales formula c> =SUM(M. 800:ESTEEM)
Example c> =SUM(B5:E9)
 . ., ,....
Bagri Computer's .. . .. ... .. .... .. ..... ... .... .. ......... .. Bagri Computer's ... .... .. . ... .. , .... ............ .
Exercise 2. E11ercl!IC 3.
The RAJDEEP PLAZA Departmental store has organized a clearance sole. The details of the items on Write the workbook CON.XLSX and calculate the amount payable. The calculated payment should be
sale and the percentage of discount offered are entered in a worksheet as shown below. Enter the given displayed in Column I.
data in a worksheet and save the workbook as TRY .xlsx. The payment can be calculated by multiplying the total number of hours consultant has worked in the
week and the pay rate. The consultants are paid Rs. 200/- per hour.
,,)
·,,,"·- c·
.. '
A
'
I ' B
I, D
D~
1 RAJDEEP PLAZA I A
I ~·~~ C E l' G
'
,H
. : .
I
2
I 1 TIMES
TOT.
J CLEARANCE SALES NAME MON TUE WED THU FRI SAT Payment
2 Hours
4
3 ASHISH · 2 6 8 8 2 ~ ,6 32 6400.00
ITEMS ORIGINAL DISCOUNT SELLING -
4 JAIDEEP ·5 5 5 4 5 5
5 NAME PRICE (%) PRICE A
5 LAUT 7 4 6 5 7 4
6
6 AJAY I 8 4 7 I 8
7 CARDS 10.00 3 9.70
7 MUKESH 9 9 6 6 9 9
8 WATCHES 3000 5
8 RAJESH 2 2 3 2 2 2
9 PENCIL 1.25 8
9 VUAY 6 1 7 I 6 I
10 PEN 25.00 2
10 HARJSH 8 3 2 5 8 3
1) RINGS 12.65 4
11 MAYANK 7 5 4 8 6 3
12 DOLLS 35.49 9
12 Total 47
13 Total
Selling price formula q =Original Price - Original Price * Discount / 100 Total hours formula q =SUM(MON : SAT)
Eumple q =B7-B7*C7/100 Eumple q =SUM(BJ:G3)
Total formula q =SUM(Cards Price : Dolls Price) Payment formula q =Product(Total Hours,200) & =Total Hours * 200
Eumple q =SUM(B7:B12) Eumple Q =Product(H3,200)
Total formula q =SUM(Asbisb Hours : Mayank Hours)
Eumple q =SUM(BJ:B11)
 Bagri Computer' s ...... ......................... .. .. . .. ... Bagri Computer's .. ..... ........ .. ... ...... . .. . .. ...... .. .
Exercise 4. Exercise S.
The results of monthly tests for class IX of the SAINI PETER' S SCHOOL are stored in a workbook. Prepare the salary sheet for the month of January. The data for the Basic Salary and other allowance
The data for the first monthly test are as shown below. are as shown below.
Result should be done Note :- Net Salary will be made Basic Salary+ T .A.+ H.R.A. + Bonus - P.F.
Average >=70 .A
Average >=60 B otherwise or <60 C ,:_A ;:<. "'
B "' C D I E
I F G
' 1 Salary Sheet
A B C D E F G Basic
I
T.A. H.R.A. Bonus P.F. Net
Name
2 Salary 5¾ orB.S. 8¾ orB.S. 7°/4 orB.S. 12¾orB.S. Salary
11 SAINI PETER HIGH SCHOOL, JAIPUR
3 Mukesh 6S00
2 RESULT SHEET 4 Sunil 7500
3 5 Ramesh 6000
Student Name 6 Neeroj 15500
4 Exam 1 Examl Exam3 Exam4 Avenge Gnide
7 Suraj 16200
5
8 Harish 8000
6 AMIT 85 87 82 66 9 Raju 9000
7 ANTSH 78 88 89 BS 10 Sumer 15000
JI Ajay 12S00
8 RAJESH 79 67 S6 78
11 Sanjay 15300
9 RO.HIT 35 40 30 27
12 Mohan 8500
JO MUKESH 86 50 70 90 13 Total:-
11 DINESH 88 89 87 92
12 SNEHA 32 3S 85 80 Travelling Allowance formula q =Basic Salary* T.A. /100
Example q =B3*5/100
q House Rent Allowance formula q =Basic Salary * H.R.A. /100
Average formula =Average(Exam 1 : Exam 4)
q Example q =B3*8/100
Example =Average(B6:E6)
Bonus formula q =Basic Salary• Bonus /100
Grade formula q =lf(Average>=70, "A", lf(Averag~, "B", "C"))
Example q =B3*7/I00
Example q =lf(F6 >=70,"A",If(F6>=60,"B","C"))
Provision Fund formula q =Basic Salary* P.F. /100
Example q =B3*12/100
Net Salary formula q =SUM(Basic Salary : Bonus, - P.F.)
Example q =SUM(B3:E3,-F3)
 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. Bagri Computer's ........... .... ......................... .
Exercise 6.
Count Present, Absent, Received Salary and Deducted Salary for the Month of January. Employee details & salary given bellow.
H
. :i . A !l:IB I!'

II
, , ,c '1tli~;1~~iJd!~4tGI
• c~ '-"-'§; '. , .fW' ., . r~,~J
H-~ ·1'<· #);, ~
K IL
GI,
Attendance Sheet
M
~
-.i,i !iO
fl•
,,,._.
0
P
• '
Q R
...
s ·. :~ T I_ u '[ V
.,_ 1
I ~, Received Deduction
Name Post Salary 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Present Absent
Salary Salary
2
Ram Teacher 15000 p p p A p A p p p p p A p p p 12 3 6000 1500
3
Ajay M.D. 12000 p p p A p A p p p p p A p A p
4
i> Vijay Postman 5000 p p A A p p p p A p p p p A p
5
Mohan Account 13000 p p p A p A p p p A p p A p p
6
Suresh H.R. 15000 p p p A p A p p p p p p p p p
7
Rakesh Clerk 8000 p p A A p A p A p p p p p p p
8
Prakash Sales Man 12000 p p p p p p p p p p A p p A p
9
Kailash Lab Ass. 17000 p p p A p p p p p p p A A A p
10
Ramesh C.A. 14000 p p A p p p p p p p p A A A p
11
I
Vikash Principal 21000 p p p A p p p p p p p p p p p
12
 ........ ...... .. ....... ..... .. ... ...... .. Bagri Computer's .... ...... ... ..... . .. ..... .. ....... ..... .
Present formula Q =COUNTIF(fotal data range of Present & Absent,"P")
Example q =COUNTIF(D3:R3, "P")
Absent formula Q =COUNTIF(fotal data range of Present & Absent,"A")
Example q =COUNTIF(D3:R3,"A")
Received Salary formula Q =Salary*Prese!Jt/30 (30 =Total days of month)
Example q =C3*S3/30 (30 = Total days of month)
Deduction Salary formula Q =Salary* Absent/30 (30 = Total days of month)
Example Q =C3*T3/30 (30 = Total days of month)
 .................................. ....... Bagri Computer's ................................. ........
Exercise 7.
Count and Sum the special number out of all the numbers.
A B C D E F G H I J K L M N 0 p Q R
1 Special Character Sheet
I
Sam able Countable
Sum able and countable Number Data
2 Result Result
3 9 5 9 5 9 9 5 9 11 13 15 25 9 9 15 9
4 18 10 18 12 18 18 8 12 18 13 15 25 22 18 18 13
25 15 25 65 25 10 25 9 11 13 25 15 9 22 25 18
5
11 20 11 25 11 11 12 18 11 13 11 25 9 11 15 11
6
22 12 22 22 15 35 22 9 11 22 25 22 12 40 20 22
7
Sum able result formula c> =SUMIF(All data range,"9")
Example c:> =SUMIF(B3:P3, "9")
Countable result formula c:> =COUNTIF(All data range,"9")
Example c:> =COUNTIF(B3:P3, "9")
 ,
.... ......... .............. ... ....... .... Bagri Computer's ............... ... ...... ... .............. Bagri Computer' s ....................................... ..
Exerdse8.
Exercise 9.
Prepare a project sheet for the year 2014 to 2018. The data of the previous year Sale and expense as Prepare a mark sheet for the year 2018 of the I.G.C.S.M. Computer Education. The data for the all
shown below. Calculate the projected revenue, total expenses and net profit for the year 2014 to 2018. student are as shown below.
P = Previous Year
Percent >--60 First
R Rate oflnnatlon (With %)
Condition c> Percent >--48 Second
.. ~~,_/"' A . ":t. J::f, '?C.• ·,11(
~J ;f~ -~~-,·,; .-w.·c::·-·"' . ~ >~\~/.'.if ;iZft"s;, ~..
»··'~il'i'"¾,,~ wr,G'ill_.:ui
. ~- Percent >=36 Third otherwise or <36 Fail
;, . !9:l:?-~
:~ - ~f{:!jt,;J, < ,,,.,, p = ii-~ ; , ;,,!l, ~~ -~ ,• m,.~- J
*.-t
J .,. ,. Project Sheet of Sales 2014 to 2018.
,,.,
:.~• MRF TYRES ,-~- .1t~~ ~~,;;i. B J.',~,1. ., .; ;-1:0~
i-;?,li~~ -""""
zz:.J'2•.~
'i4", ~~
::,_ ; · 1,+J, ~~...
"'
. ;!!~;If
f
.,:,z_ ,
d·/,'~~
..,..·
,
'J; ~ f"' ·•j,
~- f ,Z,.,, . ,
tll r• • Go<l~· heft .iJ
. •t~ . , '-~)'~ f ~
:~-~
·1: SALES PROJECT FOR 11-IE YEARS 2014 to 2018. }l Mark Sheet for the year 2018.
f{f RATE 2014 2015 2016 2017 2018 ~ Name D.O.B. Hindi English Math's ~ed Pen:eut Division
t lfi Sales 12% 23896.00 €3t Suresh 10/1/1990 78 58 62 198 66 FU"Sf
~i
;:i•,6y;
CostofSales -5% 13697.19 ?.i Rarnc~h 511!1 '192 56 70 63
'}.~ Revenue 10198.81 t-1 Ajay 1/9/1986 48 52 55
6£,:.
Mi Expenses Name & Inllatlon Percentage rJ Vijay 9/9/1 '195 80 70 59
:tt MORTGAGE 0% 2000.00 fi Aman
~ ,.
1012/1988 23 30 33
)l MAINTENANCE 2% 2939.21 ii Mahesh l/1/1987 72 53 60
jj,
_.;,.!,,.
UTILITIES 4% 743.00 t: Jaishri 21s11'l93 38 42 47 •
~~:?"~
WAGES 7% 4735.00 ,1f R.akesh 8/1/1986 52 60 61
ff INSURANCE 10% 3400.00 ,'fr1
.,.., Rohit 10/5/1990 59 61 65
t,f TOTAL EXPENSES
~5 ;J}
NET PROFIT

13817.21
.)6Ul.4
/~-
q:j'i{ !';akash 12/1/1985 67 58 63
.Recdved No. for. Q -Swn(Hinoi, English, Math's)
Sales rormala of2015 c> •P+P"R Eumple Q -Sum(C3,D3,E3)
Example (2015) Q -cs+c5*85 Percentage for. q •Recdved No.*100/300
Revenue formula Q • SaJe5..Cost of Sales
Eumple Q •F3*100/300
F,umple (201S) Q -cs-a
Division formula Q •IF(Pe~,"First" ,Jl(Per>-48,"Second",If(Per>-=36,"Third", "Fall")))
Total Expenses i.~rmaJa Q -SUM(Mortpge:.lmurance)
Q -SUM(C9:Cl3) Eumple q •IF(GJ>-60,"First",JF(Gl>-48,"Second" ,IF(G:3>=36,"Third","Fail")))
Example (2015)
Net Profit (20JS) Q •Reveaue-Total Expenses
Example (20JS) Q =C7-C14)
\
 ......................................... Bagri Computer's ....................... : ............... ..
"
Exercise 10.
Calculate the total security amount, total service charge & total Bill amount of following A.V.V.N.L. client
A B C D E
I F C I G
I H
I I
I J
I K
I L
1 Ajmer Vidhyut Vitran Nigam Limited
Connection Connection · Total Services Meter Total Ser. Total
Clint Name . City Dist Contact No. Meter
Type Security Security Charges Box Char. Charges Amount
2 Security
:3 Sandeep Sharma Nagour Nagour 01594-223550 Comm 3500 700 4200 360 450 810 5010
4 Sanjana Merta Nagour 01594-223320 Dom 2260
5 RahuSinha Degana Nagour 01594-223250 Dom 2260
6 ManojRoy Jayal Nagour 01594-223156 Agri 3050
-
7 Rajesh MaJhotra Didwana Nagour 01594-223256 Agri 3050
8 Mnkesh Tandan Kuchaman Nagour 01594-223782 Dom 2260
, 9 Rakesb Sain Ladnun Nagour 01594-223568 Comm 5010
10 Razzak:Khan Makrana Nagour 01594-223453 Dom 2260
··- .. -~ ~ ~ .. - ..
Security Charges Meter Security ' Charge
Connection Type Connection Security Connection ,Security .,Service Meter Box
,;a
Condition q Commercial 3500 Commercial :,i:~'lllol
· 700 '' 360 450
. ~ ,,_ . 1.f. -
........ ,,_
~
~- ,
2000 Agriculture 500 250 .. 300
Agriculture
xi -
Domestic · 1500 .c··· Domestic ' J ,~''h:
.• /~.. 350 ll' .150
~
260
'
 .................. .................. ..... ~agri Computer's .................. .................. .. •··
1. Connection security formula Q =IF(Conn. Type="Comm","3500",IF(Conn. Type ="Agri","2000",IF(Conn. Type ="Dom","1500")))
Example Q =IF(E3="Comm","3500",IF(E3="Agri","2000",IF(E3="D om","l500")))
2. Meter security formula Q =IF(Conn. Type="Comm","700",IF(Conn. Type="Agri","500",IF(Conn. Type="Dom", "350")))
Example Q =IF(E3="Comm","700",IF(E3="Agri","500",IF(E3="Dom","350")))
3. Total security formula Q =Connection Security + Meter Security
Example Q =F3+G3
4. Service charge formula Q =IF(Conn. Type="Comm", "360",IF(Conn. Type="Agri", "250" ,IF(Conn. Type="Dom"," 150")))
Example Q =IF(E3="Comm","360",IF(E3="Agri","250",IF(E3="Dom","150")))
5. Meter Box charge formula Q =IF(Conn. Type="Comm", "450" ,IF(Conn. Type="Agri", "300" ,IF(Conn. Type="Dom", "260")))
Example Q =IF(E3="Comm", "450" ,IF(E3="Agri", "300" ,IF(E3="Dom", "260")))
6. Total service charge formula Q =Service Charge + Meter Box Charge
Example Q =13+J3
7. Total Amounts formula Q =Total Security+ Total Service Charge
Example Q =H3+K3
 Bagri Computer's B.:g• ;·:::e,· ~
Exercise 11. Exercise 12.
Prepare a bonus sheet for the year 2014 of the IGCSM Computer Education Employees. The data for Prepare a 1inance sheet for the yenr 2014 c f th~ I.<.,., ~: M . L111npu!l': i :<'.rn0
the all employees are as shown below. student are as shown below
H.R.A. lfSalary ><=15000, "1000" otherwise "800" P.M.T. ( Per Month Transition)
Condition Q
Bonus if Salary >-15000, tbeo 15% of Salary otherwise 10•;. of Salary.
I
A u
I C
I D E I
J.
A
I B
I C D
I E F I Finance Shret
I
2 Name Post

SalaryShett
Salary ll.R.A. Bonu, Net Satu,
2
1-- -
J

:'lliimc
llnrish

P11ymrnt
2~0000

Inte rest
Rate
9% 3
·
\~a.:_ __:_ I\~--\
7<J.J9 93

·1 ·; ,,
,\1,:: . .
.i~·,., ; .,
3 Ajay Teacher 9000 800 900 10700 - Mt, •sh 158000
---
s,"u
--· - 5 1- --
4 Barish MD. 45000 -" --- -· ---
!, Smtja) 610000 7% ., I .
5' Himanshu Postman 4500 - -· -·------- - .
-
I
-·- - I---·-
6 ,\jny 890000 l 1
i~ I\
6 MBhcsh Accountnnt 13000 - 7 Mohun 1"')0000 12%
I
_ ____
2 ..
7 Mohan HR , 15000 - II Znk1r
- 1587000 12.50%
_,
5
I
• Mukcsh Clerk 12000
9 Snm,'l'r 7'10000 8.50%
-·
8
- -·
~
9 Nozir
Snmccr

Soles Mon.
Lab Ass

12000
sooo
-- ,_.._ __ .:.._ - -- -
'-
f-.
10
'-··---··
Nn,ir 25R00ll
- ,- -
7¾ I
~- -- -1
10
-- ~---- II Mukc:sh 650000 9% .j
·-
11 Sanjay CA 35000 ~- I- -- ---· --- __ .,
- -- i
I

12 llimnnsu
I 1sooou 10'), (i
12 Zak1r Principal 21000
P.M.1.formuln q =PI\IT(lnt. H •te/12,\'earn . .. 1p1~1,' ; n 1:i i
11.R.A.formula Q •IF(Salal')->-15000,"1000","800")
Example q =-PMT( C3/11,ll3 • O
Enmple q •IF(CJ>-15000,"1000","800")
Total Amount formula q •· l'l\rl *\'EAR*l2
Donut fonnula q •IF(Sal1ry>-tSOOO,Sal1ry*lS/IOO.Salacy•t0tlOO)
Example q =F.J*OJ• 12
q •IF(C3:-l5000,C3*1S/10,C3"10/100)
E-.:amplt Totiil Interest fnr .,,uhi Q "-Total ,\111011111-r:,: 111cn1
q -Salary+HRA+Bonus
Net Salary faamplr '~ =fl-lB
q -CJ+D3+EJ
Example
 ......................................... Bagri Computer's ............... .. .... ... .............. .. . ......... .. . . . ................... .. ...... Bagri Computer's ................................. .
Exercise 13. Exercise 14.
Prepare a Vat Register for the first quarter of 2013-14. Calculate the vat and total value of all Invoice. Prepare a final salary sheet for the following employee. Basic salary of employee are shown below
Purchase value and vat rate arc as shown below.
H.R.A. If salary><= 20000/-then 2000/- otherwise 1500/-
A
l B C D E F - G
Condition ca> I P.F. lfsalary >-20000/-tben 12.s•;. of Basic Salary otherwise 10•1. of Basic salary.
Net salary will be made Basic+ RR.A.-P.F.
Good Luck Motor Agency
1
Invoice Purchase Vat Invoice
2 Party Name Tin No. Vat Rate
J;

No.
S87 Salllllbh Auto Agcrv:,y 81017S3045

Value
1510.00 14%

Value Value A B
I C D E F
Final Salary Sheet
1
4 S79 Rathi Sales 8101756855 325.00 5%
J.P.F.No Employee Name Basic Salary H.R.A. P.F. Net Salary
2
5 CW/310 Computer World 8791709937 10087.72 14%
J.P.F.525 Rohit Tailor 35000
3
6 615 Salllllbh Auto Agency 81017S304S 2865.00 14%
J.P.F.635 Punit Sharma 28000
4
7 1308 ManoJ Agencies 8202703590 8108.55 5%
J.P.F.569 Akram Khan 22000
5
8 1486 Manoj Agencies 8202703590 4949.50 14%
J.P.F.362 Manoj Choudhary 16500
6
9 CW/375 Computer World 8791709937 14980.94 14%
J.P.F.880 MohitSoin 12000
7
10 615 Salllllbh Auto Agency 8101753045 2865.00 14%
J.P.F.655 Rahul Mehra 8000
8
JI 625 Ralhi Sales 8101756855 850.00 5%
12 1550 Chetan Jeweler's 8105658952 55000.00 1%
This sheet is like your test so please use your mind power.
VAT Value formula ~ -Pun:base Value*Vat Rate
Example ~ -DJ*EJ
Invoice Value formula ~ -Pun:base Value+ Vat Value
Example ~ •DJ+EJ
 . . . . . . . . . . . . . . . . . . . . . . . •. . . . . . ••. . . . . . . •. Bagri Comput(..-r' s , , ## # # I # I I##,#,# I I , , ,r # I I I , ,- ,, I , I , , , r Ii, 1,11
Exercise 15.
of conne<,1.ior~,
Create the workbo ok of BSNL Nigam Pvt. Ltd. and prepare bill of following clients according th~ t;pe
~ • - ~~
,r i .
~i ,,' .A , ill
--
• ,~',.:..~ ;.
C
;:I c ~, ~ · »~~~: _;;f7 _"'·);_r.:f!:'
, . ff' W~.
,
J'• •N!it J{•~ ~
?h
l,. o/, t/r.r.

.~ .
.fl' ;,,
a
I '/4
.
n,
'r 1
1 I J
I K
1
M
- ~- - ~ - - - ·
, 'Z!i-,
1 · B.S.N.L. Nigam Limited Jaipur (Raj.)
~ II
. tN c·ty .
0 1st
Contact Conn. Conn. Service Minimum Total Total f r~ ,.,.~bk f - Can- , 1- -,:t:i
"' Cl m ame 1
Calls f Cbarg.t / Amon m
No. Type Security Charge Charge Charges Call Calls
2
, ~ ,- Sandeep Sharma Nagour Nagour 223550 Wireless 200 650 j I ! - --
4,g Sanjana Merta
---+ - - - + -700
t-:-t- ------ --t--- -+.:-: -----t ----t- -----+ ----+ ----+200 Nagour 223320 / I -__J--
- --+--- --....l.- --.....:.-
Business
_·
~ s_ Rahu Sinha Degana Nagour 223250
, ---__ I
Home 200 620 . I ~
Nagour 223156 Wireless 200 :, _J_ f /_
.~ Manoj Roy Jayal
~-i Rajesh Malhotra Didwana Nagour 223256 Business 200 652 I I j
.·.1, , Mukesh Tandan Kuchaman Nagour 223782 Home 200 489 ' - -- I I -- -
;9;:,. Rakesh Sain Ladnun Nagour 223568 Wireless 200 570
1
1-_ ,.
:JO~ Razz.ale
·-·,-.:-
Khan Makrana Nagour 223445 Business 200 654
~1i:::
~-,--· ,,{
Pankaj Jaipur Jaipur 223448 Home 200
_ _ _..,____ _ _..,____ _ _+ - - - - + - - - -+--
850
--..___ - .
200 645
tt'2t Wireless
?if
Vijay Kumar Kalwa Jaipur 223425
-· -t-= I
-ii_tJ ~~~ .-.ic -~•
ifinimum"'t.· ··1 'free Calls
I;
I Calls I Charge per .
-l<~ ~".l\ .,.,
Call i
150 <=250 Rs. 0.5 - -1
Condition q I~WirMt'~ ~ ~op]r£~t~rr~i~sS·'~~·: .I','Ji;.~~50) . "I ;J 200
- --1
200 . 120 >250 Rs. 0.8
·-·1
i
200 t 100 >500 Rs. 1.00 I
I
 - - - - - - - ·- - ·- - - -- ---;;;--,,,--------.------.----,.._.._........,""?
1. Connection security formula q =IF(Conn. Type="Wireless", "200" ,IF(Conn. Type ="Business"," 180" ,IF(Conn. Type ="Home"," 150")))
Example q =IF(E3="Wireless", "200" ,IF(E3="Business"," 180" ,IF(E3="Home"," 150")))
2. Service charge formula q =IF(Conn. Type="Wireless"," 150" ,IF(Conn. Type ="Business"," 120" ,IF(Conn. Type ="Home", "90")))
Example q =IF(E3="Wireless", "150" ,IF(E3="Business", "120" ,IF(E3="Home", "90")))
3. Total charge formula q =Connection Security+ Service Charge+ Minimum Charge
Example q =F3+G3+H3
4. Free call on Conn. formula q =IF(Conn. Type="Wireless","150",IF(Conn. Type ="Business","120",IF(Conn. Type ="Home","100")))
Example q =IF(E3="Wireless", "150" ,IF(E3="Business", "120" ,IF(E3="Home", "100")))
5. Chargeable call formula q =Total calls - Free calls
Example q =J3-K3
6. Call charge formula q =IF(Char. call >500,Char. call*l,IF(Char•.call >250,Char. call*0.8,IF(Char. call <=250, Char. call *0.5)))
Example q =IF(L3>S00,L3*1,IF(L3>250,L3*0.8,IF(L3<=250,L3*0.5)))
7. Total Amount formula q =Total Charge + Calls Charge
Example q =D+M3
 Exercise 16.
Exercise 17. "
Prepare a temperature chart for the following cities. Prepare an age chart for the following student names.
'\.·,,~
'¥A~~ ·1
<;J:.t' .: '-
-....-.. $<i l
~. :.;;~ - ~
~,.:tr . I
''<'.-~-~-- ,i- ·
~~--~'ifj~~j :~" ~ ilp~··:, ·i•i·.
/ : .r·,;~. <_ : ):· ~-~ .~-~-<;- _ :.,.~1~},;.,, ,,_ . : ...~--~~;f;-i~:
1' ,.
.~~
~: "I' ,. .<
~;),,.~z:i-. ~
~
~
< .:B'
"~" " I - .
~ '>/,.
.
Jr· ·--
,
/---~ -:..rv,;,~
-~C;.•W ,,._ ~¼
-~ffe_.,,;;; - ,a 4¼:.;i:;•
""'"
i'
., -'-
~::
"~
Temperature Sheet ,91:' Age Chart
Qty Name Minimum Temp. Maximum Temp. £ '1 Sr. No. Name Age
:1 ~ Sr.No. 20
"' "' '! 18 2S ~--··
~~,;· Ramesh
13:' 1
'Ill-
Delhi
,41,;
'1 ,,,,. 2 Rakesh 18
'. 4 ' 2 Mumbi 20 29 f~s%-,
;< '91
~- ~ 3 Dioesh 21
~- 21 28
'
15f
~ ;..:.
3 Patna 4 Punit 15
-~ 4 Jaipur 19 26 ~ 5 Samit 22
,i'.tf
··•- .,,,, s Nagpur 20 27 't,1,-i,-J 6 Naveeo 19
~i·. ,;. ~ ,.,,,
"'.~•~;
~

,A,<;;
.. -.. 6 Ahmdabad 22 30
'lo.9'
·jl<,·
'flO
7
8

Kamal
Kapil

24
21
tJ,:;;,,:;-\ '.
7 Rajkot 17 2S
(i L11 9 Ajay 17
8 Ranchi 18 29 Manish 23
'i!.( 12 10
. ;. .
,?_.,_tr.
>
?•~:'.-..;\
9 Ko\kata 23 30
~~
Chart
Chart
Age
60
50
■ Temprature Chart
40 Max.
30 ■ Temprature Chart
30tnesh
10%
20 Min.
10
0
~
~ ~
f i 5. '-5.
'-
·a;
~ 0
.51:
.jf ~ j 6Naveen
9%
·- f
C)
a. ~ a: ~ ~
~
 ~~~m2022-23
- w~mx r~~cp T~fcl cRur
~~
SP.I ~ 11R'ran~ cpJ "ll'fl"CR\wl'i ~ fun R,~a I M\li~fll<o- ~
~ 3l91<A"/
~- ~ cpj "'fill • m:r I . _. ;,iq:,o1 ~/ 1 ~ ' ' frI'Pl
UIEH
.,
1:BI. - • . - "'i: . &IB • cgR
-- .,; _
~ 'QI
cb~c~
~
I 3fcITT q5T ~
~ 1,ifatj~'i
t~;r~:-~
I :~ -

m s:i,'Ji ere m1f

1 I 20785 ~~ - ~ ~~
2 I 20786 ~ .JliJfl¥ ~,JjjJfl¥ 45 4~
9 -- 9 I 4..., I 9 I 4
3I20787 I~~ 1 xll=lcYll<1 ~ 52 10 5 10 -I 5 I 10 - I §
41 20788 l~cgliR xfrc=rrxll:r 38 . 8 : 4 "I 8 I 4 I 8- I 3
51 20789 ~ 6fAT ~~ 35 8 I 3 I 8 I 3 I _8 I 3 ~
,.
6 i 20790 ~ 3Tlcblx~<1 46 10 I 4 I 10 I 4 I 10 I -~ 4
7 20791 3ffiUT~ ~~
~
- 12 5 I 1
2 I 5 I 2 I 5 ~1- 2
8 I 20792 I ~ cgliR flcllxl~
"' \ 25 7 I 2 I 8 ! I 3 I 8 I ·- 4
If Total Grater then 80 then Grade A+
If Total Grater then 75 then Grade A
- I
Lf Total Grater then 60 then - Grade B .
1
i \
'~
~
l
\
If Total Grater then 50 then Grade ~ <; - \
If Total Less then 50 then Need Improve
'·
1Rx
C\
c5 ~ ~ ~ ~ -;,m "cbT \1YlflJI cfRI
 ,.
A I B I C D E F G H I I I J I K I L
- 1
2
Sr. No. Student Name English Hindi
-

Students Mark Sheet

Science Co_mputer Matli
To.tal
Percentage
-
Result Grade Gift
Number . .-
1 Anil
-
3 89 98 97 90 ·- 99
4 2 Samer 65 87 89 78 80
5 3 Mahesh 85 47 96 / 89 - 82
~
~ -
6 4 Zakir : 12 ~ 15 18 ""-40 "~A2 :;;
7 5 Kamal 45 30 - 60 48 46. !
- '
8 6 Sanjay 58 59 52 55 57 -
-
9 7 Mukesh 80 30 13 18 50 ~
8 98 97 -
-10 Aman 90 92 91
\
-
..11 9 Josaf 65 87 89 80 85 .,.
-- -
12 10 Sumit 85 47 85 82 83 -
--
I
13 ' 11 Kaja! 95 50 55 98 61 - - ~
- -
14 12 Harish i
21 47 45 80 44 -- ~
'
---
15
~
- -
..
16 1. Count Total Number of all Students.
r
17 2. Count Percent of all Students. I •
l
3. Result if Percent >=36 then Pa~s otherwis2'Fail
-18 \
-19 4. Distribute in to grade. (Grade should be more then 90 (A+), more then 80 (A), more then 70 (B+), more then 60 (B), otherwise (C)
~
-20 5. Gift Prize C -
If Grade A+ 5100 If Grade A 4100
-21 - - \ -.
-
I
22 If Grade B+ 3100 If Grade ff l
' I 2100 ,
I
23 If Grade C 500 \
i
'
Use File and Path name in footer.
 ............•• •• •••••••• ••••••••••. •••• ••• •••••••••••••• Bagr,· Computer Centre .............. ..... ... ............................. .,,J ...
-
BCC Education's
-
Employee ID BCC/123 BCC/124 BCC/125 BCC/126 BCC/127
..
Employee Name Aahil Divya Jyoti Abhi Raju

~ate of Joining 23/ 11/2020 1/7/W22 5/2/2022 1/12/2020 15/08/2022

Salary 25000 30000 18000 35000 11000

~

Designation Manager I.T. Manager Councelor Trainer Operator

,/
Data with IDookup
,
--- ,.

,
Employee Bonus
Employee-ID Salary P.F. Net Salary
Name 5%

BCC/123 Aahil 25000 1250 2750 23500

BCC/124

ij.e'C/lZS -
BCC112'6-

BCC/127
'

Bonus S¾ _of Salary

P.F. Salary >=20000 then 11 % of Salary otherwise 7% of Salary

Employee Na~e =HLOOKUP(Employee ID,Data Range,Employee Name,Exact)

Example =HLOOKUP(A10,A2:F6,2,0)

Salary =HLOOKUP(Employee ID,Data Range,Salary,E.xact)

Example =HLOOKUP(Al 0,A2:F6,4,0)

Bonus =Salary*Bonus/100

Example =Cl0*.5/100
-

Provisonal Fund =if(Salary>=20000 then 11 % of salary otherwise 7% of salary)

Example =IF(Cl 0>=20000,Cl 0*11/100,Cl 0*7/100)

Net Salary· =Salary+Bonus-P.F.

Example =Cl0+D10-ElO

US£ FIL£ AND PATH NAME IN FOOTER.