Statistical Function

Runner Distance (m.) Time (Minute)

soni 400 2.12
chandni 400 1.75
vandna 400 1.25
akash 400 1.15
Rahul 400 1.25
Bhawna 400 2.15
neha 400 2.5
teena 400 1.95

Rank
Bhawna 2.15 7 1- used for ascending order
0- used for descending order

Runner Time (Minute) Distance (m.)

soni 2 425
chandni 2 195
vandna 2 325
akash 2 500
Rahul 2 248
Bhawna 2 395
neha 2 375
teena 2 385

Bhawna 395 3

Information Funciton

isnumber ISTEXT ISODD ISEVEN N ISERROR

32 1 0 0 1 32 0
15 TRUE FALSE TRUE FALSE 15 FALSE
24 TRUE FALSE FALSE TRUE 24 FALSE
FALSE FALSE Err:504 Err:504 0 FALSE
150 Kmt FALSE TRUE Err:504 Err:504 0 FALSE
James FALSE TRUE Err:504 Err:504 0 FALSE
150 km #VALUE! FALSE FALSE #VALUE! #VALUE! #VALUE! TRUE

Distance Time (hrs) Speed Iserror?

150 km 2 try again TRUE
200 2 100 FALSE
150 2 75 FALSE
350 2 175 FALSE
240 km 2 try again TRUE

Cell

address $D$54
file 'file:///conversion/tmp/activity_task_scratch/758407558.xlsx'#$inrformation
row 57
col 5
width 17

errortype

#NULL! 1
#DIV/0! 2
#VALUE! 3
#REF! 4
#NAME? 5
#NUM! 6
#N/A 7
Anything else #N/A
 32
415
85
150
650 10
25 50
14 40

Max 650 80
Min 14
average 195.85714
2nd Highest Large 415 45
2nd Lowest Small 25

Median 85

isblank t
0
FALSE
FALSE
TRUE
FALSE 150 Kmt
FALSE James
FALSE #VALUE!
$inrformation
Date Product Location SalesMan Customer Units Sales
8-Aug-16 Sunshine Midwest Pham Levis 69 3,341.00
11-Mar-16 Sunshine Midwest Pham TTT 65 3,890.00
9-Jun-16 Quad Midwest Sioux DFR 63 413.00
19-Jul-16 Sunset West Chin WT 60 1,999.00
19-Jun-17 Sunset West Smith Jockey 58 1,431.00
20-May-16 Quad West Chin ZAT 55 3,406.00
9-Jun-17 Sunshine East Franks KBTB 49 3,209.00
21-Mar-16 Sunset West Pham FM 47 2,179.00
8-Aug-16 Sunshine East Pham Jack & John 43 327.00
9-Jul-16 Carlota West Franks YTR 40 3,776.00
8-Aug-17 Quad South Smith GRR 40 1,414.00
30-May-17 Quad East Chin Levis 39 1,663.00
10-May-16 Carlota Midwest Smith Levis 29 598.00
17-Sep-16 Quad East Sioux DFR 24 3,378.00
31-Mar-17 Quad West Pham PLOT 24 2,068.00
29-Jul-17 Sunset West Chin BBT 24 554.00
1-Mar-16 Quad West Sioux Jack & John 23 3,476.00
19-Jun-16 Quad South Chin Jockey 22 4,983.00
27-Sep-16 Quad Midwest Franks Levis 22 687.00
29-Jun-17 Quad South Sioux Levis 19 1,690.00
8-Aug-16 Quad South Pham WFMI 10 4,299.00
7-Oct-16 Carlota East Franks JAQ 9 396.00
19-Jun-16 Quad Midwest Smith Jockey 8 4,575.00

Units Sales
43 1000
65 3890 TOTAL 34,697.00
24 554 MAX 4,983.00
60 1999 MIN
10 4299 AVERARE
39 1663 COUNT
55 3406
9 396

Calculate the sum of units where sales >2000

130

Calculate the sum of units where sales between 1000 to 2000

142

23
212
 32
Rose
Red
Wihte Count no. of cells containing text value
2121
4213
Code

4
 SUBTOTAL (Used in Filtered data or Hidden Data)

Ctrl + Shift + L : Filter

F4 : Repeat Last Action
Alt + = (Auto Sum) Subtotal (In filtered Data)

Caclulate the sum of Sales for Location

West 18889

Total Sales for Product

Sunset 6163

Count the No. of sales by Salesman

Chin 5

Calculate the Sum of Sales of Product in Location

Product Location Sales

Quad East 5041

5041 0
Count the number of Sales of Product in Location

Product Location Count

Quad West 3

Calculate the Sum of Sales for salesman in Location

Location Salesman Sales

West Chin

Calculate the sum of Sales for Salesman in Location

Location South

Salesman Sales
Chin 4983
Sioux 1690
Franks 0
Pham 4299
Smith 1414
West
South
East
Midwest
 Sales Report

Price 3000 8500 12000 6500 12500

Date Nokia1100 Sony x2 SumsungA7+ Sony z1 HTC DESIRE Total Sales
1-Aug-14 5 2 7 1 2 147,500.00
2-Aug-14 1 1 4 4 85,500.00
3-Aug-14 2 1 2 2 4 101,500.00
4-Aug-14 3 1 6 60,000.00
5-Aug-14 2 2 4 3 2 115,500.00
6-Aug-14 1 2 1 34,000.00
7-Aug-14 5 6 4 5 2 171,500.00
8-Aug-14 4 4 3 4 3 145,500.00

Grand Total

ALT + = 861,000.00
 147500

Running sales Total

147,500.00
233,000.00
334,500.00
394,500.00
510,000.00
544,000.00
715,500.00
861,000.00
3,740,000.00
 Math Function
ABS
-54 54 54
4 4
54 54
-414 414

CEILING
1 2 5 10
71.041 7
415.54 5 5
451 8 10
12.1574
124.45

Floor
1 2 5 10
74.041
415.54 5 2.5
451
3.745 4
123.45 6

even odd
15 16 SQRT
25 26 37
36 36 25 169 13
14 14
16 16 225 15
225
fact factdouble
5 =FACT(B33) =1*2*3*4*5 15
6 720 =1*2*3*4*5*6 48

quotient mod (Remainder value)

17 5 3 2
2

Bottle in stock Bottle/crate Need Crate? Remaining Bottle

1300 24 ,=QUOTIENT(B41,C41 4
54
4

3 2 1
 Exp Budget Actual Diff.
Electricity 2500 2650 150
Entertainment 3600 2330 1270
Travel 4000 4200 200

175 199 75
1122 1199 22
395 399 95
485 499 85

85 99
74 99

power
8
4 1024
16777216

4096 4096
0 4
0 16
0 4
0 36
 Text Function

Char
32 &
67 !
97 " Y
90 o
35 r
148 A
212 S

UPPER lower
sheetal MEHRA SHEETAL MEHRA sheetal mehra
Srhlook HOMES SRHLOOK HOMES srhlook homes
REENA singh REENA SINGH reena singh
jacob Greene JACOB GREENE jacob greene

left RIGHT
TOM watson TOM watson
Srhlook Holmes Srhlook Holmes
REENA singh
1279815740

CONCATENATE

First Name Last Name Full Name

Bhawna Singh Bhawna Singh
Tom Alexander Tom Alexander

exact
37723 37723 TRUE
Hello hello FALSE
hello hello TRUE

TRIM It removes extra space between words

Bhawna singh Bhawna singh
this a cat this a cat
REENA singh REENA singh

REPT

Reliance Reliance Reliance Reliance Reliance Reliance

case sensitive
FIND

That is a cat. 6
THIS IS A PEN. 3

Separating first name & last name

Tom Alexander Tom Alexander
Srhlook Holmes Srhlook Holmes
Tom Rom Tom Rom
Akash Sharma Akash Sharma

Tom Alexander
Tom Franklin

SUBSTITUTE

All cats are black. All cats are dogs. All foxes are black. All foxes are dogs.

919858279144
919858278744 0985827044
All cats are black. All cats are dogs.

replace
All cats are black. All cats are dogs. All cats are black. All foxes are dogs.

919858279144
919858278744

TEXT

7-Dec-15 DAY

lekpkj fgUnqLrku uUuqsdh½

Ravinder Singh R. Singh

Sagar Sharma S. Sharma

0.984871 98.49%
0.487488 48.75%
0.475787 47.58%
Code ASCII CODE SYSTEM American Standard Code for Information iNterchange
38 32- 255
32 h A TO Z 65 - 90
89 a to z 97- 122
111 0 to 9 48-57
114
65
83

PROPER
Sheetal Mehra
Srhlook Homes
Reena Singh
Jacob Greene

MID LEN
Mw
Hol

& Ampersand
Bhawna Singh Bhawna Singh
Tom Alexander Tom Alexander

Reliance Reliance Reliance Reliance Reliance

not case sensitive
search

6
 3

Alexander 4 9
Holmes Alexander
Rom
Sharma
#VALUE!
Alexander
Franklin
#VALUE!

25

MONTH

98.49%
48.75%
47.58%
rmation iNterchange
 Loan Calculation Alt, D, L => Data Validation ( List )

Periodicity
Loan Amt. 200,000.00 Quarterly
Annually Interest Rate 15%
Effective Rate 3.75% =C7/G6 Yearly
Loan Period (years) 5 Half-yearly
No.of installment 20 =C9*G6 Monthly
Quarterly
Scheduled Installment ₹ 14,392.42 =PMT(C8,C10,-C6) Bi-monthly

Total Interest Paid 87,848.39 =(C12*C10)-C6

1
Inst. NO. Opening Bal. Interest Schd. Pymt Closing Bal.
 - 0.00 14392.42 (14,392.42)
(14,392.42) -539.72 14392.42 (29,324.55)
(29,324.55) -1099.67 14392.42 (44,816.64)
(44,816.64) -1680.62 14392.42 (60,889.69)
(60,889.69) -2283.36 14392.42 (77,565.47)
 Description
Calculates the payment for a loan based on constant payments and a constant int
L => Data Validation ( List ) Syntax
PMT(rate, nper, pv, [fv], [type])
Periodicity
The PMT function syntax has the following arguments
4
Rate Required. The interest rate for the loan.
1 Nper Required. The total number of payments for the loan.
2 Pv Required. The present value, or the total amount that a series of future payme
12
known as the principal.
Fv Optional. The future value, or a cash balance you want to attain after the last p
4 omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
6 Type Optional.
The number 0 (zero) or 1 and indicates when payments are due.

PPMT
Principal value for inst number
8
=PPMT(C8,G22,C10,-C6)
IPMT
Interest value for inst number
8
IPMT(C8,G26,C10,-C6)
a loan based on constant payments and a constant interest rate.
e])
s the following arguments
rate for the loan.
umber of payments for the loan.
alue, or the total amount that a series of future payments is worth now; also
ue, or a cash balance you want to attain after the last payment is made. If fv is
0 (zero), that is, the future value of a loan is 0.
d indicates when payments are due.
 Rep

All cats are black. All cats are dogs.

919858279144
919858279144
12-34-56-78
john cena
 Replace & Substitute Function

Len Replace Substitute

38 All cats are black. All fox are dogs.
29 9858279144 9858279144
+919858279144 +919858279144

Double Replace
johncena@gmail.com
 Vlook up Function
Look up Value First Name Last Name Phone No Address
108 Zubi khan 8347471330 Zubi

Look up Value First Name Last Name Phone No Address

104 Mohd Danish 8210824056 Trilok puri

Look up Value First Name Last Name Phone No Address

104

Emp.I'd First Name Last Name Phone No

101 Kamal singh 9173515943
102 A Rajput 7612904650
103 Punam Sharma 8065497756
104 Mohd Danish 8210824056
105 Nakul Rajput 8792082332
106 Kiran Sharma 9420720713
107 Hena Khan 9399707173
108 Zubi khan 8347471330
109 Jay Singh 8606278822
110 Vinith Joshi 7725805717

Emp.I'd First Name Last Name Phone No

111 manno singh 9173515943
112 Rajat Rajput 7612904650
113 Punam Sharma 8065497756
114 Mohd Danish 8210824056
115 Nakul Rajput 8792082332
116 Kiran Sharma 9420720713
117 Hena Khan 9399707173
118 Rubina khan 8347471330
119 Jay Singh 8606278822
120 Vinith Joshi 7725805717
 Vlook up satands For "Vertical
lookup".It is a function that search
Net Salary for a certain value in a column( that
Zubi so called "table arrray")

Net Salary
25373
Emp.I'd Iserror Iferror

Net Salary 115 1 retry

8792082332
Address Net Salary
Mayur Vihar 23915
Patparganj 33691
Patparganj 34387
Trilok puri 25373
Patparganj 20529
New Ashok Nagar 22960
Trilok puri 31280
Trilok puri 34049
Mayur Vihar 24556
New Ashok Nagar 28265

Address Net Salary

Mayur Vihar 23915
Patparganj 33691
Patparganj 34387
Trilok puri 25373
Patparganj 20529
New Ashok Nagar 22960
Trilok puri 31280
Trilok puri 34049
Mayur Vihar 24556
New Ashok Nagar 28265
 Hlook up Function

Lookup value 101

First Name
Last Name
Phone No
Address
Net Salary

Emp.I'd 101 102 103

First Name Kamal Rajat Punam
Last Name singh Rajput Sharma
Phone No 9173515943 7612904650 8065497756
Address New Ashok Nagar New Ashok Nagar New Ashok Nagar
Net Salary 23915 33691 34387

`
ook up Function

104 105 106 107 108

Mohd Nakul Kiran Hena Rubina
Danish Rajput Sharma Khan khan
8210824056 8792082332 9420720713 9399707173 8347471330
New Ashok Nagar New Ashok Nagar New Ashok Nagar New Ashok Nagar New Ashok Nagar
25373 20529 22960 31280 34049
 109 110
Jay Vinith
Singh Joshi
8606278822 7725805717
New Ashok Nagar New Ashok Nagar
24556 28265
CHOOSE

Score Name Score Score remarks Score remarks

1 mohit 6 Poor Amit
2 abhiasha 1 Very Poor Suresh
3 Zara 8 Poor Zara
4 pari 2 Averge Aman
5 aman 6 Averge John
6 sujal 4 Averge Mani
7 yash 6 Averge Mannu
8 depanshi 9 poor kamal
9 vanshika 5 very good Punam
10 zoya 6 excellent Radha

Score remarks Name

Poor Amit
Poor Suresh
Poor Zara
Averge Aman
Averge John
 Data Validation
S.No First Name Last Name Gender Age Date of Birth State District
10 8-Dec-98 Delhi India gate
5 Delhi India gate
7 Delhi Mayur vihar
5 Delhi India gate
5 Delhi India gate
5 Delhi India gate
5 Delhi India gate
5 Delhi India gate
5 Delhi India gate
5 Delhi India gate

1/1/1996
1/1/2000

Text Numbers Text Dates Numbers

mon 10 mon 5/27/2024 445
tue 20 tue 5/28/2024 180
wed 30 wed 9/28/2021 445
thu 40 thu 9/29/2021 219
fri 50 fri 9/30/2021 321
sat 60 sat 10/1/2021 352
sun 70 sun 10/11/2021 256
mon 80 mon 9/29/2021 551
tue 90 tue 9/30/2021 445
wed 100 wed 10/1/2021 180
 Delhi Gender
Trilok puri Male
Preet vihar Female
CP
Indraprastha
Mayur vihar
Seelampur
India gate
Qutub Minar
Red fort

<300
>200
<200 or >300
double text
Mon to wed
Date function
=TODAY() 27-May-24 12-Oct-24
Text
=TEXT(C3,"d") =TODAY() =TEXT(TODAY(),"dddd")
=TEXT(D3,"ddd") Mon
=TEXT(D3,"dddd") Monday
=TEXT(D3,"mmm") May
=TEXT(D3,"mmmm") May 2024
=TEXT(D3,"dd-mm-yyy") May-27-24 10
=TEXT(D3,"yy") 24y 12
=TEXT(D3,"yyy") 10/12/2024

=DATE(F9,F10,F11)

5/27/2024
=EDATE(D3,2*2) 27-Apr-24
=EOMONTH(D3,2*2) 31-Mar-24

=NOW() 06:00:16 AM
=HOUR(D22) 6
=MINUTE(D22) 0
=SECOND(D22) 16

=TIME(D23,D24,D25) 6:00 AM
Monday

=YEAR(F3)
=MONTH(F3)
=DAY(F3)
=DATE(F9,F10,F11)

10/12/2024

Dob Now
28-08-2009 5/27/2024

#NAME?
 Employee Salary Sheet

Shift + F2 : Insert/ Edit Comment

Emp-id Emp-name Designation HRA Payable

1456 David Sales Mang. yes

1457 Deepak Sales Mang. yes
1458 mithun Sales Exe. no
1459 ansu Sales Mang. yes
1460 Priya Sales Exe. no
1461 Kanika Jr. Accountant yes
1462 Rahul account yes
1463 Suman Sales Exe. no
1464 Neha account yes
1465 Mukul account yes

HRA Payable yes

HRA
TA
DA
Gross salary
PF
ESI
Total
deduction
Net salary
Professional
Tax
oyee Salary Sheet

Allowance Deduction
40% 10% 9% 12.50%

basic salary HRA TA DA Gross salary PF

15,000.00 6000 1,500.00 1,350.00 23,850.00 2,981.25

30,000.00 12000 3,000.00 2,700.00 47,700.00 5,962.50
15,000.00 0 - - 15,000.00 1,875.00
23,000.00 9200 2,300.00 2,070.00 36,570.00 4,571.25
6,230.00 0 - - 6,230.00 778.75
12,400.00 4960 1,240.00 1,116.00 19,716.00 2,464.50
17,200.00 6880 1,720.00 1,548.00 27,348.00 3,418.50
15,000.00 0 - - 15,000.00 1,875.00
24,000.00 9600 2,400.00 2,160.00 38,160.00 4,770.00
12,000.00 4800 1,200.00 1,080.00 19,080.00 2,385.00

=IF(C7="sales exe.","no","yes")
=IF(D7="yes",E7*$F$5,0) Payable Sal. =M7-N7
=IF(D7="yes",E7*$G$5,"----")
=IF(D7="yes",E7*$H$5,"***") paid salary =IF(P7>=26,O7,(O7/26)*P7)
=SUM(E7:H7)
=I7*$J$5
=I7*$K$5

=J7+K7
=I7-L7

=IF(M7>15000,200,IF(M7>12000,"150",IF(M7>8000,100,IF(M7>5000,60,0))))
 Working Day : 26
Deduction
2.75%

Days
Total Professional
ESI Net salary Payable Sal. Paya
deduction Tax
ble

655.88 3,637.13 20,212.88 #NAME? 26

1,311.75 7,274.25 40,425.75 26
412.50 2,287.50 12,712.50 24
1,005.68 5,576.93 30,993.08 16
171.33 950.08 5,279.93 19
542.19 3,006.69 16,709.31 25
752.07 4,170.57 23,177.43 20
412.50 2,287.50 12,712.50 26
1,049.40 5,819.40 32,340.60 18
524.70 2,909.70 16,170.30 19

F(P7>=26,O7,(O7/26)*P7)
paid salary

- Tax Slab RATe

0
From Upto T. value
5000 0
5001 8000 60
8001 12000 100
12001 15000 150
15001 above 200
 Marksheet

Roll No student Maths Physics Chem Bio Eng Hindi Obtained M max. Mark
1252 Christina 55 66 85 76 54 96 432 500
1257 Anna 84 66 37 87 92 48 414 500
1251 Davik 79 83 82 95 31 76 446 500
1261 Chanchal 88 90 84 76 69 61 468 500
1262 James 88 88 59 92 48 54 429 500
1259 Jennifer 91 91 46 100 91 80 499 500
1258 Katherine 31 61 62 40 84 49 327 500
1260 Kerry 93 69 82 67 77 36 424 500
1255 kuldeep 83 80 92 92 62 85 494 500
1254 Kunal 95 55 82 77 37 78 424 500
1256 Mohit 45 81 66 81 44 43 360 500
1253 Nitin 70 56 55 44 43 91 359 500

1/1/2022 1/2/2022 1/3/2022 1/4/2022 1/5/2022

Sat Sun Mon Tue Wed
student p p p p
Christina a p p a
Anna p p p a
Davik a p p p
Chanchal l p a p
James a p l p
Jennifer p p a p
Katherine p p p l
Kerry
kuldeep
Kunal
Mohit
Nitin
Percentage Result Grade Result/ SUBJECT
40% fail D pass
83%
89%
94%
86%
100%
65%
85%
99%
85%
72%
72%

P A L T
=COUNTIF(E23:H23,"P") 0 0
2 2
3 1
3 1
2 1
2 1
3 1
3 0
 Sum Max Min large small Average
47
69
100
37
91
56
40
57
34
50
 IF formulas example
a person is eligible for vote whose age is above 18.

Student Age < <=

James 18 eligible not eligible
David 17 not eligible
Andrew 18 eligible
Nitin 18 eligible
Katherine 21 eligible
Jennifer 20 eligible
Kerry 17 not eligible
Garry 15 not eligible

Comparative (Logical operator)

< Less than

> Greater than
>= Greater than equal to
<= Less than equal to
<> not equal to
= equals to

Exact And equal

List A List B
XYZ XYZ
XYZ XYZ
ABC ABC
abc ABC

Separating first name & last name

First Name
Tom Alexander =LEFT(D39,FIND(" ",D39,1)-1)
Srhlook Holmes Srhlook
Tom Rom Tom
Akash Sharma Akash
ulas example

> >= = <>

not eligbile not
not not
not eligbile
not eligbile
eligbile eligbile
eligbile eligbile
not not
not not

Exact = if(exact) =if(=)

1 1 yes same
TRUE 1 yes same
TRUE TRUE yes same
FALSE TRUE no same

st name & last name

Last Name
=RIGHT(D39,LEN(D39)-FIND(" ",D39))
Holmes
Rom
Sharma
Text Numbers Text Dates Numbers
mon 10 mon 5/27/2024 445
tue 20 tue 5/28/2024 180
wed 30 wed 9/28/2021 445
thu 40 thu 9/29/2021 219
fri 50 fri 9/29/2021 321
sat 60 sat 9/30/2021 352
sun 70 sun 10/10/2021 256
mon 80 mon 9/29/2021 551
tue 90 tue 9/30/2021 445
wed 100 wed 10/1/2021 180
<300
>200
<200 or >300
double text
Mon to wed
 Employee Timing Sheet
Reg. HRs
Reg.Rate/Hrs
OT/Rate/Hrs

Emp- Name Login Lunch Start Lunch End Logout Worked Hrs
Anser 9:00 AM 1:00 PM 1:30 PM 7:00 PM =((E7-B7)-(D7-C7))*24
David 10:00 AM 12:00 PM 12:30 PM 3:00 PM 4.50
Jenifer 8:30 AM 12:30 PM 1:00 PM 5:00 PM 8.00
Raj 9:00 AM 1:00 PM 1:00 PM 6:00 PM 9.00
James 10:00 AM 2:00 PM 2:30 PM 4:00 PM
Harry 9:30 AM 1:30 PM 2:00 PM 5:00 PM
Rakshit 9:00 AM 1:00 PM 1:30 PM 7:00 PM
Rushal 9:00 AM 1:00 PM 1:30 PM 6:30 PM
Pranjal 9:00 AM 12:00 PM 12:30 PM 5:30 PM
Rohan 9:00 AM 1:00 PM 1:30 PM 6:15 PM

9:00 AM 1:30 PM

Ctrl + Shift + 2 : Time Format

8:00 AM 4:00 PM 8

0.3333333333
ng Sheet
8
90
100

Reg. Pay O.T O.T Pay Gross Pay

=IF(F7>8,8*$G$3,F7*$G$3) =IF(F7>8,F7-8,0) =H7*$G$4 =G7+I7
405 - - 405.00
720 - -
720 1.00 100.00 820.00
0 - - -
0 - - -
0 - - -
0 - - -
0 - - -
0 - - -