Advanced Excel Functions
Basic Functions Sum
Product
Average
Count
Max
Min
Financial Functions PV Present Value
Module 1
FV Future Value
PMT Constant Payments
NPER Number of Period
RATE Rate of Interest
NPV Net Present Value
Returns an equivalent interest rate for the Module 2
RRI growth of an investment (or) CAGR
IRR Internal Rate of Return
XIRR Extended IRR
MIRR Modified IRR
Math Functions ABS
SUBTOTAL
SUMPRODUCT
SUMIF & COUNTIF
LEN & TRIM
CONCATENATE
IF combined with AND / OR
VLOOKUP & HLOOKUP
Index and Match
Choose
EDATE
EOMONTH
Advanced Excel Functions
ABS - Absolute Value of number
Year 1 2
Revenue 100 120
Expenses -180 -140
Profit/Loss 80 20
Subtotal
Branch 1
1 2
Revenue 250 487
Expenses -200 -412
Profit 50 75
Branch 2
Revenue 330 465
Expenses -230 -350
Profit 100 115
Total Profit 150 190
SUMIF, COUNTIF, Sumproduct
Product Category Quantity
HP Laptop Laptop 125
Scan disk Pendrive - 64 gb Pendrive 950
Mouse Others 542
ADATA Harddisk - 1TB Harddisk 220
Powercable Others 500
Webcam Others 56
Dell Laptop Laptop 35
HP Pendrive - 64 gb Pendrive 1250
Transcend Harddisk - 2 TB Harddisk 150
EDATE
Project Start Date 3/15/2019
Project period (months) 3
Project end date
EOMONTH
Project Start Date 3/30/2019
Project period (months) 3
Project End date
End TEXT TO COLUMN
DR JAIN
21 MBAR 10567
DR ARUN ROY
100
-20
3 4 80
125 145 -40
-112 -165 120
13 20
1 400
3 4 -200
546 698 200
-354 -546 2 500
192 152 -100
400
3 600
-250
589 654 350
-510 -625 OVERALL 950
79 29
271 181
Price Sum
33456 4182000 SUMIF - Laptop
825 783750
856 463952 COUNTIF - Others
3354 737880
1450 725000 SUMIF - Endswith "s"
2199 123144
36545 1279075 SUMPRODUCT
750 937500
5895 884250
Complex Number
Project Start Date 3/15/2019 154687988
Project period (months) 3 154879654
Project end date 154879654
DR JAIN
#NAME?
100
20
80
40
240
=SUBTOTAL(9,H17:H18)
=SUBTOTAL(9,H20:H21)
=SUBTOTAL(9,H23:H24)
=SUBTOTAL(9,H17:H25)
BBBBBBB
5461075 =SUMIF(C36:C44,"LAPTOP",F36:F44)
1721250 =SUMIF(C36:C44,"PENDRIVE",F36:F44)
3 =COUNTIF(C36:C44,"OTHERS")
1312096 =SUMIF(C36:C44,"*s",F36:F44)
10116551 =SUMPRODUCT(D36:D44,E36:E44)
Simple Number
15.4687988
1548.79654
5
IF COMBINED AND or OR
IF, Eligble for Bonus is >=2000 Basic
IF & AND Basic >=2000 and Exp >=15 Years
IF & OR Basic >=2000 or Exp>=15
IF & AND & OR Basic >=2000, DA>=1200, or Exp>15)
NAME DEPT BASIC DA HRA OA TOTAL Experience IF DA IF BONUS
Xavier Mech 2000 1600 250 750 4600 20 E 200
Francis Sales 1565 1252 250 800 3867 15 NE NE
Raman Finance 1250 1000 250 650 3150 18 NE NE
Narendra Operations 2500 2000 250 800 5550 16 E 250
Sunil Stores 2120 1696 250 750 4816 12 E 212
BASIC>=2000 OR EXP>15 AND TOTAL >3500
BASIC>=2000 AND EXP>15 OR TOTAL>3500
IF BASIC N BONUS IF & AND IF & OR IF&AND&OR IF&AND&OR2
920 E E E E
773.4 NE E NE NE
630 NE E E NE
1110 E E E E
NE NE E E E
VLOOKUP & HLOOKUP - Match & Index
VLOOKUP VLOOKUP
Product Category Quantity Price Sum Price of Mouse 856
HP Laptop Laptop 125 33456 4182000 Qty of Webcam 56
Scan disk PPendrive 950 825 783750
Mouse Others 542 856 463952
ADATA Hard Harddisk 220 3354 737880
Powercabl Others 500 1450 725000
Webcam Others 56 2199 123144
Dell Lapto Laptop 35 36545 1279075
HP PendrivPendrive 1250 750 937500
Transcend Harddisk 150 5895 884250
HLOOKUP
Product HP Laptop Scan disk PMouse ADATA Hard
Powercable Webcam Dell Lapto HP Pendriv
Category Laptop Pendrive Others Harddisk Others Others Laptop Pendrive
Quantity 125 950 542 220 500 56 35 1250
Price 33456 825 856 3354 1450 2199 36545 750
Sum 4182000 783750 463952 737880 725000 123144 1279075 937500
=VLOOKUP("MOUSE",B5:F14,4,FALSE())
=VLOOKUP("WEBCAM",B5:F14,3,FALSE())
Transcend Harddisk - 2 TB HLOOKUP
Harddisk Price of Mouse
150 Qty of Webcam
5895
884250
Choose Function
Item Code Category Code Category
Cash and Cash Equivalents 2 1 Fixed Assets
Stock 2 2 Current Assets
Creditors 5 3 Shareholder's Funds
Bank Loans 4 4 Non-Current Liabilities
Share Capital 3 5 Current Liabilities
Reserves and Surplus 3
Equipments 1
Furniture 1
Current Investment 2
Outstanding Salary 5
Date Month Function Choose
19-Jan-21
12-Feb-21
16-Mar-21
28-Apr-21
12-May-21
15-Jun-21
18-Jul-21
13-Aug-21
14-Sep-21
16-Oct-21
21-Nov-21
16-Dec-21
Company Profitability LevelScore Rating Score Rating
HDFC 2 1 Average
TVS 4 2 Good
Reliance 3 3 V.Good
Tata 2 4 Excellent
Infosys 4
Wipro 1
Powergrid 1
PNB 4
ent Liabilities
Data Validation
Goto Data Tab - Data Tools - Select Data Validation
Validation Criteria Options
Default Any value
Whole number
Decimal
List
Date
Time
Text length
Custom
Addin is Input message and Error Alert
WHOLE NUMBERS DECIMAL LIST DATE TIME TEXT LENGTH
26
28
25
30
29
28
27
25
30
29
Employee Database
EMPNO NAME DEPT BASIC DA HRA OA TOTAL Experience
F00102 Xavier Mech 2000 1600 250 750 4600 20
F00105 Francis Sales 1565 1252 250 800 3867 15
F00108 Raman Finance 1250 1000 250 650 3150 18
F00215 Narendra Operations 2500 2000 250 800 5550 16
F00364 Sunil Stores 2120 1696 250 750 4816 12
