1.functions and Formulas

Download as xlsx, pdf, or txt
Download as xlsx, pdf, or txt
You are on page 1of 11

Product Unit PriceQuantity Sales

Premium Pen 50 1247 62350


Regular Pen 20 310 6200
Premium Pencil 40 2846 113840
Regular Pencil 15 989 14835
Eraser 25 2047 51175
Sharpner 20 1042 20840
Long Thick Notebook 90 2565 230850

Short Thick Notebook 45 644 28980

Long Thin Notebook 60 2450 147000

Short Thin Notebook 30 1827 54810

Functions (Sum, Count, Average, Max, Min, Sumif, Countif, Sumifs, Countifs)
Total Sales ₹ 730,880.00
Total Quantity 15,967
Number of Products 10
Average Quantity 1,597
Average Sales ₹ 73,088.00
Maximum Sales ₹ 230,850.00
Minimum Sales ₹ 6,200.00
Total Sales of Notebooks ₹ 461,640.00
Number of Products having sales
more than Rs. 1 lac 3
Total Sales of Products having unit
price more than Rs. 40 ₹ 469,180.00
Number of Products having sales
more than Rs. 1 lac and quantity
more than 2500 2
Count Number of Orders 10
ountif, Sumifs, Countifs)
AND OR NOT TRUTH TABLE
5 10 0 1 FALSE
20 10 1 0 TRUE T
T
F
IF Condition if(logical_condition, [value_if_true],[value_if_false]) F

Age 16 25
Solution Cannot Vote Can Vote T
T
Marks 30 85 F
Solution Fail Pass F

Name Score 1 Score 2 Score 3 Total Grade


Subhash 70 75 60 205 A
Chandra 80 93 63 236 A
Omkar 90 89 85 264 O
Rajesh 13 83 28 124 F
Ramesh 68 71 38 177 B

IF Function (Single IF, Nested IF, IF Using AND)


AND
T T
F F
T F
F F

OR
T T
F T
T T
F F

Score Grade
<140 F
>=140 and <200 B
>=200 and <260 A
>=260 O
1 2 3 4 5 6 7 8 9 10
2 4 6 8 10 12 14 16 18 20
3 6 9 12 15 18 21 24 27 30
4 8 12 16 20 24 28 32 36 40
5 10 15 20 25 30 35 40 45 50
6 12 18 24 30 36 42 48 54 60
7 14 21 28 35 42 49 56 63 70
8 16 24 32 40 48 56 64 72 80
9 18 27 36 45 54 63 72 81 90
10 20 30 40 50 60 70 80 90 100

Working with Formula using $ (Relative, Absolute and Mixed Cell Reference)
First Name Last Name Full Name
GAURAV PATIL GAURAV PATIL Concatenate
SHASHANK IYER shashank iyer Concat + Lower
Sanika Sinha SANIKA SINHA Concat+Upper
ANAGHA SHARMA Anagha Sharma Concat+Proper
ADITI JOSHI aditi joshi Concat+lower

Phone Area Code Prefix Line Number


(834)133-1776 (834) 133 1776
(623)560-7830 (623) 560 7830
(220)249-2610 (220) 249 2610
(444)698-6184 (444) 698 6184
(639)771-1953 (639) 771 1953
(761)431-5462 (761) 431 5462
(326)681-5891 (326) 681 5891

Date Joined Date Month Year


3/5/1998 5 3 1998
4/16/2003 16 4 2003
12/1/2010 1 12 2010
9/16/1999 16 9 1999
3/12/2011 12 3 2011
4/15/2009 15 4 2009
2/9/2012 9 2 2012
3/24/2004 24 3 2004
11/12/2003 12 11 2003
2/19/2012 19 2 2012

Date 12/21/2023 Today's Date


Date in US format 12/21/2023 Current Date and Time

Price 1236534.8
Price to print Rs.1,236,534.80

Phone 8104365281
Phone to print +91 81 04 365 281

Code 9345
Code to print 009345

Date Joined Date Resigned Years TD


3/1/2022 6/5/2023 1 "Y"
15 "M"
461 "D"
4 "MD"
3 "YM"
96 "YD"

Date Joined Date Resigned Years TD


3/5/1998 8/7/2018 20 (Practise it the same way as above table)
4/16/2003 4/3/2018 14
12/1/2010 3/4/2017 6
9/16/1999 5/3/2019 19
3/12/2011 9/2/2018 7
4/15/2009 4/1/2016 6
2/9/2012 4/5/2019 7
3/24/2004 8/4/2016 12
11/12/2003 2/3/2016 12
2/19/2012 2/1/2015 2

Text Function
Sweta 0 isblank
#DIV/0! 1 iserror
123 0 istext
Sweta 0 isnumber
#DIV/0! Error iferror
5
Text Functions
Upper, Lower, Proper, Concatenate, Left, Right, Mid

Date Functions
Day, Month, Year, Text, Datedif

Information Functions
IsBlank, IfError, IsText, IsNumber

7/29/2024
7/29/2024 19:15

Difference in complete years


Difference in complete months
Difference in days
Difference in days, ignoring months and years
Difference in months, ignoring days and years
Difference in days, ignoring years

e it the same way as above table)


Loan Amount 10,000,000 Working with Formula using $ (Relative, Absolute and Mixed Cell Re
Annual Interest Rate 8%
No. of Months 60
EMI ₹ -202,763.94

Month Premium Principal Interest


1 ₹ -202,763.94 ₹ -136,097.28 ₹ -66,666.67
2 ₹ -202,763.94 ₹ -137,004.59 ₹ -65,759.35
3 ₹ -202,763.94 ₹ -137,917.96 ₹ -64,845.99
4 ₹ -202,763.94 ₹ -138,837.41 ₹ -63,926.53
5 ₹ -202,763.94 ₹ -139,762.99 ₹ -63,000.95
6 ₹ -202,763.94 ₹ -140,694.74 ₹ -62,069.20
7 ₹ -202,763.94 ₹ -141,632.71 ₹ -61,131.23
8 ₹ -202,763.94 ₹ -142,576.93 ₹ -60,187.02
9 ₹ -202,763.94 ₹ -143,527.44 ₹ -59,236.50
10 ₹ -202,763.94 ₹ -144,484.29 ₹ -58,279.65
11 ₹ -202,763.94 ₹ -145,447.52 ₹ -57,316.42
12 ₹ -202,763.94 ₹ -146,417.17 ₹ -56,346.77
13 ₹ -202,763.94 ₹ -147,393.28 ₹ -55,370.66
14 ₹ -202,763.94 ₹ -148,375.90 ₹ -54,388.04
15 ₹ -202,763.94 ₹ -149,365.08 ₹ -53,398.87
16 ₹ -202,763.94 ₹ -150,360.84 ₹ -52,403.10
17 ₹ -202,763.94 ₹ -151,363.25 ₹ -51,400.69
18 ₹ -202,763.94 ₹ -152,372.34 ₹ -50,391.60
19 ₹ -202,763.94 ₹ -153,388.15 ₹ -49,375.79
20 ₹ -202,763.94 ₹ -154,410.74 ₹ -48,353.20
21 ₹ -202,763.94 ₹ -155,440.15 ₹ -47,323.80
22 ₹ -202,763.94 ₹ -156,476.41 ₹ -46,287.53
23 ₹ -202,763.94 ₹ -157,519.59 ₹ -45,244.35
24 ₹ -202,763.94 ₹ -158,569.72 ₹ -44,194.22
25 ₹ -202,763.94 ₹ -159,626.85 ₹ -43,137.09
26 ₹ -202,763.94 ₹ -160,691.03 ₹ -42,072.91
27 ₹ -202,763.94 ₹ -161,762.31 ₹ -41,001.64
28 ₹ -202,763.94 ₹ -162,840.72 ₹ -39,923.22
29 ₹ -202,763.94 ₹ -163,926.33 ₹ -38,837.62
30 ₹ -202,763.94 ₹ -165,019.17 ₹ -37,744.78
31 ₹ -202,763.94 ₹ -166,119.30 ₹ -36,644.65
32 ₹ -202,763.94 ₹ -167,226.76 ₹ -35,537.19
33 ₹ -202,763.94 ₹ -168,341.60 ₹ -34,422.34
34 ₹ -202,763.94 ₹ -169,463.88 ₹ -33,300.06
35 ₹ -202,763.94 ₹ -170,593.64 ₹ -32,170.30
36 ₹ -202,763.94 ₹ -171,730.93 ₹ -31,033.01
37 ₹ -202,763.94 ₹ -172,875.80 ₹ -29,888.14
38 ₹ -202,763.94 ₹ -174,028.31 ₹ -28,735.63
39 ₹ -202,763.94 ₹ -175,188.50 ₹ -27,575.45
40 ₹ -202,763.94 ₹ -176,356.42 ₹ -26,407.52
41 ₹ -202,763.94 ₹ -177,532.13 ₹ -25,231.81
42 ₹ -202,763.94 ₹ -178,715.68 ₹ -24,048.27
43 ₹ -202,763.94 ₹ -179,907.12 ₹ -22,856.83
44 ₹ -202,763.94 ₹ -181,106.50 ₹ -21,657.45
45 ₹ -202,763.94 ₹ -182,313.87 ₹ -20,450.07
46 ₹ -202,763.94 ₹ -183,529.30 ₹ -19,234.64
47 ₹ -202,763.94 ₹ -184,752.83 ₹ -18,011.12
48 ₹ -202,763.94 ₹ -185,984.51 ₹ -16,779.43
49 ₹ -202,763.94 ₹ -187,224.41 ₹ -15,539.53
50 ₹ -202,763.94 ₹ -188,472.57 ₹ -14,291.37
51 ₹ -202,763.94 ₹ -189,729.06 ₹ -13,034.89
52 ₹ -202,763.94 ₹ -190,993.92 ₹ -11,770.03
53 ₹ -202,763.94 ₹ -192,267.21 ₹ -10,496.73
54 ₹ -202,763.94 ₹ -193,548.99 ₹ -9,214.95
55 ₹ -202,763.94 ₹ -194,839.32 ₹ -7,924.63
56 ₹ -202,763.94 ₹ -196,138.25 ₹ -6,625.70
57 ₹ -202,763.94 ₹ -197,445.83 ₹ -5,318.11
58 ₹ -202,763.94 ₹ -198,762.14 ₹ -4,001.80
59 ₹ -202,763.94 ₹ -200,087.22 ₹ -2,676.72
60 ₹ -202,763.94 ₹ -201,421.14 ₹ -1,342.81
ative, Absolute and Mixed Cell Reference)

You might also like