What is a Formula in Excel?

Formula is a process
Input--->Calculate--->Output

INPUT WAYS (3 WAYS)

1. MANUAL 60
2. REFERENCE 60
3. FORMULA 60

FORMULAS ARE 3 TYPES BASIS ON ARGUMENTS

1. LIMITED ARGUMENTS FORMULAvlookup, sumif, countif, match
2. UNLIMITED max, sum, average
3. NO ARGUMENTS today, now

ARGUMENTS (2 types)
1. MANDATORY
2. [OPTIONAL ARUGMENT] 25

DATA TYPE
INPUT DATA TYPE YOU CAN GIVE
TEXT TEXT/STRINGS/TEXT FORMATTED N
SERIAL NUMBERS/ DATES /TIME DATE/TIME
NUMBER/INDEX/k/NUM NUMBERS
RANGE/ARRAY/CELL REF REFERENCE
LOGICAL OUTPUT TRUE/FALSE
VALUE ALL OF THE ABOVE

Microsoft Excel 2016

 10

10
20 20
30

INNER FORMULA IS GIVING AN APPROPRIATE OUTPUT

TO THE OUTER FORMULA 30

8/15/2023 8/15/2023 6:43

3 MICROSOFT EXCEL NEW VERSION

MICROSOFT EXCEL NEW VERSION

DATA TYPE
INPUT REQUIRES
"TEST" - DOUBLE QUOTES FOR MANUAL INPUT YOU GIVE
"07/06/2014" - DOUBLE QUOTES FOR MANUAL INPUT 25 6/25/2018
337 83
56 22
Using comparision operators. <,>,<=,>=,=,<> Name99 12
NAME7 73

Microsoft Excel 2016

CROSOFT EXCEL NEW VERSION
CROSOFT EXCEL NEW VERSION

Try to get CAN from B29 cell using MID formula

MICRO CAN EXCEL
6 2018 16
12 48.142857 77

FAIL

NAME1 83 PASS
NAME2 56 PASS
NAME3 48 PASS
NAME4 43 PASS
NAME5 22 FAIL
NAME6 12 FAIL
NAME7 73 PASS
Type of Reference Styles Example
1. Relative No $ symbols A1
2. Absolute 2 $ symbols $A$1
3. Mixed Reference i. Row Freeze $ For Row A$1
ii. Column Freeze $ For Column $A1

1. Relative

EMP ID Emp Name Designation Basic Salary HRA Deductions

E00001 Suresh Associate 60000 12000 15000
E00002 Kaveri Lead 39000 19500 9750
E00003 Sai Developer 14000 7000 3500
E00004 Venkat Tester 10000 5000 2500
E00005 Ramesh DB 36000 18000 9000
E00006 Deepak Manager 35000 17500 8750
194000 79000 48500
 2. Absolute Ref Style F4 as shortcut (1 time)

DIS % 10%

Net Salary PRODUCT PRICE NET PRICE

57000 PRO1 50000 5000 45000
48750 PRO2 30000 3000 27000
17500 PRO3 27133 2713 24420
12500 PRO4 85446 8545 76901
45000 PRO55 67082 6708 60374
43750 PRO6 80197 8020 72177
224500 PRO7 44232 4423 39809
PRO8 76791 7679 69112
PRO9 17970 1797 16173
PRO10 40467 4047 36420
PRO11 83851 8385 75466
PRO12 8654 865 7789
TOTAL 550640.7
3. Mixed Reference
ROW FREEZE & COLUMN FREEZE

2 3 4 5
2 4 6 8 10
3 6 9 12 15
4 8 12 16 20
5 10 15 20 25
TEXT FORMULAS
Source
Microsoft Excel

CONCATENATE MicrosoftExcel Microsoft Excel Microsoft-----Excel

& Concate operator MicrosoftExcel Microsoft Excel
Source
Microsoft Excel
LEFT Micro
RIGHT Excel Microsoft Excel
MID soft

LEN 15 4

Source
miCroSoft exCeL
UPPER MICROSOFT Microsoft Excel
LOWER excel Microsoft Excel
PROPER Microsoft

Source
Microsoft Excel 2013
TRIM Microsoft Excel 2013

Microsoft Excel 2013

SUBSTITUTE Micreseft Excel 2013 MicrosoftExcel2013
MicrosoftExcel2013
Soruce
8/15/2023
TEXT Tuesday
Aug 15 Tue August, 2023 123 000123
2023 4567 0004567
15-08-2023 98876 00098876
1508 9987 0009987

Soruce
10
20
30
0
VALUE 10 a123
20 Err:502
30
60
 abc
CBA CBA

Microsoft Excel NEW VERSION RELEASED

Source
mIcroSoFT eXceL 2013
TRIM & PROPER Microsoft Excel 2013
Microsoft Excel 2013
Microsoft Excel 2013
Microsoft Excel 2013
Microsoft Excel 2013

000123 E000123 E00123

004567 E004567 E04567
098876 E098876 E98876
009987 E009987 E09987
DATE&TIME FORMULAS

Ctrl+; to get curre Ctrl+: to get current time

TODAY 8/15/2023 6/26/2018 10:53 AM

NOW 8/15/2023 6:43:14 8/1/2018 11:12

DATE 8/1/2018 2017 12 7

2018 1 12

YEAR 2018 2018

MONTH 8
DAY 1

TIME 10:45:35

Please find out how many total seconds are there in the time valu
HOUR 10 38735
MINUTE 45
SECOND 35

All days worked dayExclude Holidays

NETWORKDAYS 4/2/2018 4/20/2018 19 15 13
NETWORKDAYS.INTL 4/2/2018 4/20/2018 17 15

WORKDAY 4/2/2018 10 4/12/2018 4/16/2018

WORKDAY.INTL 4/2/2018 10 4/13/2018

DATEDIF 1/23/1992 31 31
8/15/2023 378 6
11527 23 31 Years 6 Months 23 Da

11527

DATEDIF(START_DATE,END_DATE,FORMAT)

Date calculations:
Date+Days 12/28/2017 10 1/7/2018
Date-Days 12/28/2017 15 12/13/2017
Date-Date 12/28/2017 12/1/2017 27

6/26/2018
6/29/2018
12/24/2136
 12/7/2017
1/12/2018

nds are there in the time value in the cell of B18

4/5/2018
4/10/2018 20
4/30/2018
5/1/2018

31 Years 6 Months 23 Days

LOGICAL FORMULAS

IF Target Score 40

NAME MARKS Status NAME MARKS

Name1 71 PASS Name1 71
Name2 31 FAIL Name2 85
Name3 73 PASS Name3 73
Name4 39 FAIL Name4 39
Name5 83 PASS Name5 83
Name6 64 PASS Name6 64
Name7 22 FAIL Name7 22
Name8 57 PASS Name8 57
Name9 21 FAIL Name9 21
Name10 40 PASS Name10 22

AND
OR

IFERROR 10 0 #DIV/0! Check numbers

Name Actual Sales Target Met/Not Met

Name1 56 57 Not Met
Name2 97 52 Met
Name3 77 98 Not Met
Name4 92 25 Met
Name5 92 51 Met
Name6 98 18 Met
Name7 57 62 Not Met
Name8 65 57 Met
Name9 81 22 Met
Name10 52 77 Not Met

0% 5% 20% 30%
Income Max.Tax Rate 1 2 3 4 Tax
750000 20.00% 250000 250000 250000 0 62500
150000 0.00% 0 0 0 0 0
750000 20.00% 250000 250000 250000 0 62500
500000 5.00% 250000 250000 0 0 12500
2400000 30.00% 250000 250000 500000 1400000 532500
550000 20.00% 250000 250000 50000 0 22500
1250000 30.00% 250000 250000 500000 250000 187500
700000 20.00% 250000 250000 200000 0 52500
 250000 0.00% 250000 0 0 0 0
350000 5.00% 250000 100000 0 0 5000
70000 0.00% 0 0 0 0 0
1350000 30.00% 250000 250000 500000 350000 217500
2350000 30.00% 250000 250000 500000 1350000 517500
1400000 30.00% 250000 250000 500000 400000 232500
2400000 30.00% 250000 250000 500000 1400000 532500
250000 0.00% 250000 0 0 0 0
1850000 30.00% 250000 250000 500000 850000 367500
2450000 30.00% 250000 250000 500000 1450000 547500

A 1
B 0
C 0
D 0
E 1
F 0
G 0
H 0
I 1
J 0
K 0
L 0
M 0
N 0
O 1
P 0
Q 0
R 0
S 0
T 0
U 1
V 0
W 0
X 0
Y 0
Z 0
0-34 FAIL
35-60 C GRADE
61-80 B GRADE CUTOFF
81 ABOVE A GRADE

Grade COMPLEX NOT CORRECT NAME Sub1 Sub2 Sub3

B GRADE B GRADE B GRADE Name1 100 21 85
A GRADE A GRADE A GRADE Name2 45 52 49
B GRADE B GRADE B GRADE Name3 87 94 97
C GRADE C GRADE B GRADE Name4 33 48 26
A GRADE A GRADE A GRADE Name5 98 57 73
B GRADE B GRADE B GRADE Name6 92 38 30
FAIL FAIL B GRADE Name7 21 57 88
C GRADE C GRADE B GRADE Name8 45 40 58
FAIL FAIL B GRADE Name9 93 55 39
FAIL FAIL B GRADE Name10 47 65 100

1 2 3 4 Final Tax Amount

0 12500 50000 0 62500
0 0 0 0 0
0 12500 50000 0 62500
0 12500 0 0 12500
0 12500 100000 420000 532500
0 12500 10000 0 22500
0 12500 100000 75000 187500
0 12500 40000 0 52500
0 0 0 0 0
0 5000 0 0 5000
0 0 0 0 0
0 12500 100000 105000 217500
0 12500 100000 405000 517500
0 12500 100000 120000 232500
0 12500 100000 420000 532500
0 0 0 0 0
0 12500 100000 255000 367500
0 12500 100000 435000 547500
 40

STATUS
FAIL
PASS
PASS
FAIL
PASS
FAIL
FAIL
PASS
FAIL
PASS
INFORMATION FORMULAS

Check B4 cell value is a number or not, if number then chec

ISEVEN 4 1 EVEN

ISODD 5 1

ISNUMBER 24 1 24 0
excel 0

ISTEXT excel 1

ISERROR #DIV/0! 1

ISBLANK 1 0

blank if text enter number length all correct then

Enter ID ID should bCheck the length is 6 Thank you
Employee ID 123456 Thank you

86 291 Valid
291
79
510
261
798
153
524
228
number or not, if number then check is even or odd, print the result in E4 cell
STATISTICAL FORMULAS

AVERAGE 37588.235

AVERAGEIF 26562.5 Average Net Salary of Managers Get the average net salary of Testers

Average Net Salary of Managers where the basic salary is greater than 10000
AVERAGEIFS 33333.333 Avg. net salary of Testers whose basic salary is greater than 5000
Answer 15000

MAX Get Max Net Salary

MIN Get Min Net Salary

LARGE Get the 3rd Largest Net Salary

SMALL Get the 2nd Smallest Net Salary

COUNT 10 it counts only numbers

COUNTA 11
COUNTBLANK 3
COUNTIF 4
COUNTIFS 3

RANK
NAME MARKS Rank
Name1 71 4
Name2 85 1
Name3 83 2
Name4 39 7
Name5 83 2
Name6 64 5
Name7 22 8
Name8 57 6
Name9 21 10
Name10 22 8
erage net salary of Testers

y is greater than 5000

Number count Text Count

7 3
71
85
text
39
83
value
22
57
macros
22
MATH FORMULAS

Answer
SUM get the sum of net salary from Data Set sheet 639000
SUMIF Find out the sum of mangers' salaries 106250
SUMIFS Mangers' salaries where basic salary is greater than 10000 100000

1
RAND 0.9832449 Name Marks
RANDBETWEEN 89 Name1 89 PASS
Name2 93 PASS
MEDIAN 74 Name3 48 PASS
MODE 48 Name4 33 FAIL
Name5 48 PASS
-10 Name6 88 PASS
ABS 10 Name7 90 PASS
Name8 91 PASS
Name9 74 PASS
ROUND 100.68 100.679982 Name10 23 FAIL
Name11 31 FAIL
708
Formula
639000

Sub marks Status

19 Fail
27 Fail
14 Fail
84 Pass
71 Pass
82 Pass
59 Pass
62 Pass
LOOKUP FORMULAS

1
2
3
ROW 7 4

COLUMN 2 1 2 3 4

VLOOKUP

EMP ID Emp Name Designation Basic Salar HRA Deduction Net Salary
E00017 Sam CEO 198000
E00014
E00009
E00001
E00091

VLOOKUP & COLUMN

EMP ID Emp Name Designation Basic Salar HRA Deduction Net Salary
E00017 Sam CEO 86000 25000 12000 198000
E00014 Pareto DB 17000 8500 4250 21250
E00009 John BechamDB 14000 7000 3500 17500
E00001 Suresh Associate 60000 12000 15000 57000
E00091 #N/A #N/A #N/A #N/A #N/A #N/A

VLOOKUP & COLUMN & IFERROR

EMP ID Emp Name Designation Basic Salar HRA Deduction Net Salary
E00017 Sam CEO 86000 25000 12000 198000
E00014 Pareto DB 17000 8500 4250 21250
E00009 John BechamDB 14000 7000 3500 17500
E00001 Suresh Associate 60000 12000 15000 57000
E00091 Not Found Not Found Not Found Not Found Not Found Not Found

MATCH
 1 2 3 4 5 6 7
EMP ID Deductions Emp Name HRA Basic SalaDesignatioNet Salary
6 2 5 4 3 7

EMP ID Deductions Emp Name HRA Basic SalaDesignatioNet Salary

E00017 12000 Sam 25000 86000 CEO 198000
E00014 4250 Pareto 8500 17000 DB 21250
E00009 3500 John Becham 7000 14000 DB 17500
E00001 15000 Suresh 12000 60000 Associate 57000
E00091 - - - - - -

HLOOKUP

EMP ID E00017 E00014 E00009 E00001 E00091

Emp Name Sam Pareto John Becha Suresh -
Designation CEO DB DB Associate -
Basic Salary 85000 17000 14000 60000 -
HRA 25000 8500 7000 12000 -
Deductions 12000 4250 3500 15000 -
Net Salary 98000 21250 17500 57000 -

WITH NAMED RANGE

EMP ID Emp Name Designation Basic Salar HRA Deduction Net Salary
E00017 Sam CEO 85000 25000 12000 98000
E00014 Pareto DB 17000 8500 4250 21250
E00009 John BechamDB 14000 7000 3500 17500
E00001 Suresh Associate 60000 12000 15000 57000
E00091 #N/A #N/A #N/A #N/A #N/A #N/A
Customers Table Products Table

C.Code C.Name P.Code P.Name P.Price

121 Raju 1 Hide & See 182
122 Ravi 2 Face Wash 404
123 Kumar 3 Santoor 341
124 Prashanth 4 Notebook 594
5 Laptop 364
6 TV 507
7 Mobile 422

EMP ID Emp NameDesignatioBasic SalaNet Salary

E00002
E00013
E00015
E00071
E00017

E00016
E00019
E00011
E00017
E00014
INDEX & MATCH

EMP ID Deduction Emp NameHRA Basic SalaDesignatioNet Salary

E00017 12000 Sam 25000 86000 CEO 198000
E00014 4250 Pareto 8500 17000 DB 21250
E00009 3500 John Bech 7000 14000 DB 17500
E00001 15000 Suresh 12000 60000 Associate 57000
E00091 #N/A #N/A #N/A #N/A #N/A #N/A
Txn.ID C.Code P.Code P.Price Qty Sale Value
a123 122 3 341 2 682
a124 124 6 507 1 507
a125 122 4 594 3 1782
a126 121 1 182 4 728
a127 122 7 422 5 2110
a128 123 6 507 1 507
a129 122 2 404 2 808

124 Prashanth
Net Salary
EMP ID Emp Name Designation Basic Salary HRA Deductions Net Salary
E00012 Morgan Analyst 15000 12000 10000 47500
E00001 Suresh Associate 60000 12000 15000 57000
E00017 Sam CEO 86000 25000 12000 198000
E00014 Pareto DB 17000 8500 4250 21250
E00009 John Becham DB 14000 7000 3500 17500
E00005 Ramesh DB 36000 18000 9000 45000
E00007 James Developer 21000 10500 5000 26500
E00003 Sai Developer 14000 7000 3500 17500
E00002 Kaveri Lead 39000 19500 9750 48750
E00016 Modi Manager 17000 8500 4250 21250
E00015 William Smith Manager 5000 2500 1250 6250
E00010 Thompson Manager 28000 14000 7000 35000
E00006 Deepak Manager 35000 17500 8750 43750
E00011 Jones PJM 17000 8500 4250 21250
E00013 Howard Tester 2000 1000 500 2500
E00008 Andrews Tester 14000 7000 3500 17500
E00004 Venkat Tester 10000 5000 2500 12500

EMP ID E00012 E00001 E00017 E00014

Emp Name Morgan Suresh Sam Pareto
Designation Analyst Associate CEO DB
Basic Salary 38000 60000 85000 17000
HRA 19000 12000 25000 8500
Deductions 9500 15000 12000 4250
Net Salary 47500 57000 98000 21250

EMP ID Emp Name Designation Basic Salary HRA Deductions Net Salary
E00012 Morgan Analyst 15000 12000 10000 47500
E00001 Suresh Associate 60000 12000 15000 57000
E00017 Sam CEO 85000 25000 12000 198000
E00014 Pareto DB 17000 8500 4250 21250
E00009 John Becham DB 14000 7000 3500 17500
E00005 Ramesh DB 36000 18000 9000 45000
E00007 James Developer 21000 10500 5000 26500
E00003 Sai Developer 14000 7000 3500 17500
E00002 Kaveri Lead 39000 19500 9750 48750
E00016 Modi Manager 17000 8500 4250 21250
E00015 William Smith Manager 5000 2500 1250 6250
E00010 Thompson Manager 28000 14000 7000 35000
E00006 Deepak Manager 35000 17500 8750 43750
E00011 Jones PJM 17000 8500 4250 21250
E00013 Howard Tester 2000 1000 500 2500
E00008 Andrews Tester 14000 7000 3500 17500
E00004 Venkat Tester 10000 5000 2500 12500
E00091 Test1 Desg1 1000 2000 3000 5000
 Basic SalaEmp Name Designation EMP ID HRA Deductions Net Salary
15000 Morgan Analyst E00012 12000 10000 47500
60000 Suresh Associate E00001 12000 15000 57000
86000 Sam CEO E00017 25000 12000 198000
17000 Pareto DB E00014 8500 4250 21250
14000 John Becham DB E00009 7000 3500 17500
36000 Ramesh DB E00005 18000 9000 45000
21000 James Developer E00007 10500 5000 26500
14000 Sai Developer E00003 7000 3500 17500
39000 Kaveri Lead E00002 19500 9750 48750
17000 Modi Manager E00016 8500 4250 21250
5000 William Smith Manager E00015 2500 1250 6250
28000 Thompson Manager E00010 14000 7000 35000
35000 Deepak Manager E00006 17500 8750 43750
17000 Jones PJM E00011 8500 4250 21250
2000 Howard Tester E00013 1000 500 2500
14000 Andrews Tester E00008 7000 3500 17500
10000 Venkat Tester E00004 5000 2500 12500

E00009 E00005 E00007 E00003 E00002 E00016 E00015 E00010

John Bech Ramesh James Sai Kaveri Modi William Smith Thompson
DB DB Developer Developer Lead Manager Manager Manager
14000 36000 21000 14000 39000 17000 5000 28000
7000 18000 10500 7000 19500 8500 2500 14000
3500 9000 5000 3500 9750 4250 1250 7000
17500 45000 26500 17500 48750 21250 6250 35000
E00006 E00011 E00013 E00008 E00004
Deepak Jones Howard Andrews Venkat
Manager PJM Tester Tester Tester
35000 17000 2000 14000 10000
17500 8500 1000 7000 5000
8750 4250 500 3500 2500
43750 21250 2500 17500 12500
EMP ID Emp NameDesignatioBasic SalaHRA DeductionNet Salary Sum of Net Salary
E00012 Morgan Analyst 38000 19000 9500 47500 539000
E00001 Suresh Associate 60000 12000 15000 57000
E00017 Sam CEO 85000 25000 12000 98000
E00014 Pareto DB 17000 8500 4250 21250
E00009 John Bech DB 14000 7000 3500 17500
E00005 Ramesh DB 36000 18000 9000 45000
E00007 James Developer 21000 10500 5000 26500
E00003 Sai Developer 14000 7000 3500 17500
E00002 Kaveri Lead 39000 19500 9750 48750
E00016 Modi Manager 17000 8500 4250 21250
E00015 William SmiManager 5000 2500 1250 6250
E00010 Thompson Manager 28000 14000 7000 35000
E00006 Deepak Manager 35000 17500 8750 43750
E00011 Jones PJM 17000 8500 4250 21250
E00013 Howard Tester 2000 1000 500 2500
E00008 Andrews Tester 14000 7000 3500 17500
E00004 Venkat Tester 10000 5000 2500 12500
Sum of Net Salary
539000

Avg. Net Salary

31705.88

Max. of Net Salary

98000

Sum of Deductions
 50 RandBetween
20 54
30 94
100 26
98
58
82
92
59
82
79