INDRAPRASTHA COLLEGE FOR WOMEN

DEPARTMENT OF COMMERCE

S.NO. STUDENT'S NAME MARKS 'A' MARKS 'B' MARKS 'C' TOTAL PERCENTAGE
1 Shawn Mendes 35 39 32 106 53
MATHEMATICAL FUNCTIONS 2 Taylor Swift 40 42 39 121 60.5
3 Harry Styles 39 31 45 115 57.5
4 Ed Sheeran 37 39 43 119 59.5
1. SUM & PERCENTAGE 5 Arijit Singh 31 25 50 106 53
6 Jubin Nautiyal 36 39 10 85 42.5
7 Selena Gomez 40 45 33 118 59
8 Armaan Malik 34 43 36 113 56.5
9 Alessia Cara 47 20 38 105 52.5
10 Shreya Ghoshal 22 37 40 99 49.5

NUMBER RESULT NUMBER RESULT

25 5 100.56 100.56
2. SQUARE ROOT 196 14 3. ABSOLUTE VALUE 200 200
169 13 -46 46

NUMBER RESULT NUMBER RESULT

100.56 100 500.45 500
3. INTEGER 50.46 50 4. ROUND OFF 500.5 501
209 209 300.29 300
200.79 200.8
NUMBERS 1 2 3 4 5 6 7 100.59 101
5. AVERAGE AVERAGE 4 100.23 100
 S.NO. STUDENT'S NAME DAY NUMBER
STATISTICAL FUNCTIONS 1 Shawn Mendes Monday 10
2 Taylor Swift Tuesday 20 5 SUM OF NUMBERS BELONGING TO MONDAY AND SHAWN MENDES
1 SUM OF NUMBERS < 50 100 3 Harry Styles Wednesday 30 100
2 SUM OF NUMBERS AGAINST MONDAY 180 4 Ed Sheeran Thursday 40 6 COUNT OF NUMBERS WHERE THEY HAVE MONDAY AND SHAWN MENDES
3 COUNT OF NUMBERS < 50 4 5 Arijit Singh Friday 50 2
4 AVERAGE OF NUMBERS < 50 25 6 Jubin Nautiyal Saturday 60 7 AVERAGE OF NUMBERS BELONGING TO MONDAY AND SHAWN MENDES
7 Selena Gomez Sunday 70 50
8 Armaan Malik Monday 80
9 Shawn Mendes Monday 90

S.NO. STUDENT'S NAME MARKS RESULT COMMISSION TABLE NO. 1

1 Shawn Mendes 32 FAIL SALES PERSON SALES AMOUNT COMMISSION
2 Taylor Swift 79 PASS Shawn Mendes 140802 2816.04
3 Harry Styles 88 PASS Taylor Swift 1124428 22488.56
LOGICAL FUNCTIONS 4 Ed Sheeran 35 PASS Harry Styles 1314679 26293.58
5 Arijit Singh 39 PASS Ed Sheeran 178030 3560.6
6 Jubin Nautiyal 57 PASS Arijit Singh 70392 0
1. IF 7 Selena Gomez 58 PASS Jubin Nautiyal 109202 2184.04
8 Armaan Malik 66 PASS Selena Gomez 66939 0
9 Shawn Mendes 21 FAIL Armaan Malik 99999 0
Shawn Mendes 101010 2020.2

"If Sales Amount is >= 1,00,000, then provide Commission @ 2 %"

COMMISSION TABLE NO. 2

SALES PERSON SEAT COVERS ALLOY WHEELS LED LAMPS TOTAL SALES COMMISSION 2. IF & AND
Shawn Mendes 94524 23168 45793 163485 Work Hard
Taylor Swift 76645 99316 28165 204126 Work Hard
Harry Styles 26791 23533 85320 135644 Work Hard "If Sales Amount is >= 50,000 for all products,
Ed Sheeran 65693 81914 79914 227521 4550.42 then provide Commission @ 2 %"
Arijit Singh 24451 20408 34470 79329 Work Hard
Jubin Nautiyal 88519 82818 32406 203743 Work Hard
Selena Gomez 81629 34127 49350 165106 Work Hard
Armaan Malik 72642 27828 79067 179537 Work Hard
Shawn Mendes 83228 95206 57876 236310 4726.2

COMMISSION TABLE NO. 3

SALES PERSON SEAT COVERS ALLOY WHEELS LED LAMPS TOTAL SALES COMMISSION 3. IF & OR
Shawn Mendes 94524 23168 45793 163485 3269.7
Taylor Swift 76645 99316 28165 204126 4082.52
Harry Styles 26791 23533 85320 135644 2712.88 "If Sales Amount is >= 50,000 for any product,
Ed Sheeran 65693 81914 79914 227521 4550.42 then provide Commission @ 2 %"
Arijit Singh 24451 20408 34470 79329 Work Hard
Jubin Nautiyal 88519 82818 32406 203743 4074.86
Selena Gomez 81629 34127 49350 165106 3302.12
Armaan Malik 72642 27828 79067 179537 3590.74
Shawn Mendes 83228 95206 57876 236310 4726.2
INFORMATION FUNCTIONS

DATA ISBLANK ISERROR ISERR ISNA ISEVEN ISODD

#NULL! 0 1 1 0 #NULL! #NULL!
#DIV/0! FALSE TRUE TRUE FALSE #DIV/0! #DIV/0!
#REF! FALSE TRUE TRUE FALSE #REF! #REF!
#NAME? FALSE TRUE TRUE FALSE #NAME? #NAME?
#NUM! FALSE TRUE TRUE FALSE #NUM! #NUM!
#N/A FALSE TRUE FALSE TRUE #N/A #N/A
TRUE FALSE FALSE FALSE Err:504 Err:504
TRUE FALSE FALSE FALSE Err:504 Err:504
1 FALSE FALSE FALSE FALSE FALSE TRUE
717 FALSE FALSE FALSE FALSE FALSE TRUE
545 FALSE FALSE FALSE FALSE FALSE TRUE
789 FALSE FALSE FALSE FALSE FALSE TRUE
1506 FALSE FALSE FALSE FALSE TRUE FALSE
TEXT FALSE FALSE FALSE FALSE Err:504 Err:504
0 FALSE FALSE FALSE FALSE 1 0
0 FALSE FALSE FALSE FALSE 1 0
0 FALSE FALSE FALSE FALSE 1 0
1 FALSE FALSE FALSE FALSE 0 1
0 FALSE FALSE FALSE FALSE 1 0
IFORMULA ISLOGICAL ISNONTEXTISNUMBER ISTEXT ISREF
1 0 1 0 0 TRUE
TRUE FALSE TRUE FALSE FALSE TRUE
TRUE FALSE TRUE FALSE FALSE TRUE
TRUE FALSE TRUE FALSE FALSE TRUE
TRUE FALSE TRUE FALSE FALSE TRUE
TRUE FALSE TRUE FALSE FALSE TRUE
FALSE FALSE TRUE FALSE FALSE TRUE
FALSE FALSE TRUE FALSE FALSE TRUE
FALSE FALSE TRUE TRUE FALSE TRUE
FALSE FALSE TRUE TRUE FALSE TRUE
FALSE FALSE TRUE TRUE FALSE TRUE
FALSE FALSE TRUE TRUE FALSE TRUE
FALSE FALSE TRUE TRUE FALSE TRUE
FALSE FALSE FALSE FALSE TRUE TRUE
TRUE FALSE TRUE TRUE FALSE TRUE
TRUE FALSE TRUE TRUE FALSE TRUE
TRUE FALSE TRUE TRUE FALSE TRUE
TRUE FALSE TRUE TRUE FALSE TRUE
TRUE FALSE TRUE TRUE FALSE TRUE
DATABASE FUNCTIONS

1. DSUM & DAVERAGE

SALES REPORT
COMPANY ITEMS QUANTITY
HP PC 3
Dell Laptop 7
Epson Printer 10
Sony PC 15
HP Laptop 12
Dell Printer 10
Epson Printer 9
Sony Laptop 5
HP PC 7
Epson Laptop 6
Dell Printer 5
Dell Laptop 4

ITEMS COMPANY
2. DCOUNT & DCOUNTA Laptop Sony
Printer Epson
REPORT ITEMS COMPANY DSUM (QUANTITY)
RATE TOTAL Laptop Dell 11
30000 90000 PC ___ 25
25000 175000 PC HP ___
12000 120000
30000 450000
25000 300000
12000 120000 3. DMAX & DMIN
12000 108000
25000 125000 ITEMS COMPANY MIN. PRICE (DMIN)
30000 210000 PC HP 3
25000 150000 PC Sony ___
12000 60000
25000 100000

COMPANY TOTAL RECORD

Sony 1
Epson 2
 DAVERAGE
___
___
150000

MAX. PRICE (DMAX)

___
30000
 TEXT FUNCTIONS
NAMES TRIM PROPER
LAST01 DINA LAST01 DINA Last01 Dina
LAST02 DAN LAST02 DAN Last02 Dan
LAST03 ABDISAMAD A LAST03 ABDISAMAD A Last03 Abdisamad A
LAST04 HASSAN M LAST04 HASSAN M Last04 Hassan M
LAST05 VIVIEN R LAST05 VIVIEN R Last05 Vivien R
LAST06 MICHAEL G LAST06 MICHAEL G Last06 Michael G
LAST07 CATHERINE W LAST07 CATHERINE W Last07 Catherine W
LAST08 NIKOL M LAST08 NIKOL M Last08 Nikol M
LAST09 DESIREE ALAST09 DESIREE A Last09 Desiree A

Department Mathematics

Left Dep Mat

Right ent ics
Mid partm themat
Concatenate DepartmentMathematics
Department Mathematics

FIND 5
SEARCH 6
REPLACE Computer Science
SUBSTITUTE Mathematiks
 PROPER UPPER LOWER LENGTH
na LAST01 DINA last01 dina 11
n LAST02 DAN last02 dan 10
disamad A LAST03 ABDISAMAD A last03 abdisamad a 18
ssan M LAST04 HASSAN M last04 hassan m 15
vien R LAST05 VIVIEN R last05 vivien r 15
chael G LAST06 MICHAEL G last06 michael g 16
therine W LAST07 CATHERINE W last07 catherine w 18
kol M LAST08 NIKOL M last08 nikol m 14
siree A LAST09 DESIREE A last09 desiree a 16
 LOOKUP FUNCTIONS
ID COMPANY PRODUCT
101 Dell Keyboard
102 Red Gear Mouse
1. V Lookup 103 MI Mobile
104 Samsung Laptop
105 Red Gear Head Phone
106 Lenovo Monitor

ID 101 102 103 104

2. H Lookup COMPANY Dell Red Gear Samsung MI
PRODUCT Laptop Head Phone Phone Mobile

ID COMPANY PRODUCT ID
101 Dell Keyboard 101
3. Lookup 102 Red Gear Mouse 102
103 MI Mobile 103
104 Samsung Laptop 104
105 Red Gear Head Phone 105
106 Lenovo Monitor 106
101
102
103
104
 ID PRODUCT
101 Keyboard
102 Mouse
103 Mobile
104 Laptop
105 Head Phone
106 Monitor
101 Keyboard

105 ID PRODUCT
Red Gear 101 Laptop
Mouse 102 Head Phone
103 Phone
104 Mobile
PRODUCT 105 Mouse
Keyboard 106 #N/A
Mouse 101 Laptop
Mobile 102 Head Phone
Laptop 103 Phone
Head Phone 104 Mobile
Monitor 105 Mouse
Keyboard 106 #N/A
Mouse
Mobile
Laptop
MATCH FUNCTION
COLOUR COLOUR POSITION
Green Red 3
Blue White 4
Red
White
Yellow
Orange

INDEX FUNCTION LOGIN ID

1 Hi
2 Bye
3 Goodbye
4 Adieu
5 Hola
 Position 1 (Row) 4
Position 2 (Column) 2

Login ID Adieu
 DATE AND TIME FUNCTIONS YEAR MONTH DAY DATE
2023 6 20 ###
2013 4 10 ###
0 6 21 ### DEFAULT YEAR : 1900
Creating Dates : 2010 0 4 ### DEFAULT MONTH : DECEMBER
DATE 2000 8 0 ### DEFAULT DAY : 31
1 1 1 ### YEAR should be between 1900 and 9999.
112 1 1 #VALUE! If YEAR is between 0 and 1899, then this value is added to 1900.
2012 -1 1 ### MONTH should be between 1 and 12.
2012 0 1 ### If MONTH is negative or is greater than 12, the date extends back or forward, into the previous or following year.
2012 13 1 ### DAY should be between 1 and 31.
2012 6 -1 ### If DAY is negative or is greater than 31, the date extends back or forward, into the previous or following month.
2012 6 32 Monday, July 2, 2012

DATE DAY MONTH YEAR WEEKNUM WEEKDAY DATE EDATE EOMONTH DAYS (b/w DATE and EOMONTH) DAYS360 (b/w DATE and EOMONTH)
Extracting Components of a Date : 6/20/2023 20 6 2023 26 2 ### ### ### 365 -360
DAY 4/10/2013 10 4 2013 15 3 ### ### ### 365 -360
MONTH 6/21/1900 21 6 1900 25 4 ### ### ### 0 0
YEAR 12/4/2009 4 12 2009 49 5 ### ### ### -366 360
WEEKNUM 7/31/2000 31 7 2000 32 1 ### ### ### -365 358
WEEKDAY 1/1/1901 1 1 1901 1 2 ### ### ### -245 240
1/1/2012 1 1 2012 1 7 WEEKNUM : Which week in the year 2023 contains 20 June?
11/1/2011 1 11 2011 45 2 WEEKDAY : Which day of the week is 20 June 2023? DEFAULT 1 (Sunday) to 7 (Saturday)
Performing Calculations with Dates : 12/1/2011 1 12 2011 49 4 EDATE : Returns a date that is a specified number of months before or after a supplied start date.
EDATE 1/1/2013 1 1 2013 1 2 EOMONTH : Returns the last day of the month, that is a specified number of months before or after an initial supplied start date.
EOMONTH 5/30/2012 30 5 2012 23 3 DAYS : Number of Days between 2 Dates (months in a year can be of 30 or 31 days)
DAYS 7/2/2012 2 7 2012 28 1 DAYS360 : Number of Days between 2 Dates (months in a year are of 30 days only)
DAYS360
WORKDAY
NETWORKDAYS START DATE HOLIDAYS WORKDAY NETWORKDAYS WORKDAY : Returns a date that is a supplied number of working days (excluding weekends & holidays) ahead of a given start date.
### ### ### 26 NETWORKDAYS : Number of Days between 2 dates, excluding weekends and holidays.
### ### 26
###

HRS MINS SECS TIME

5 21 25 05:21:25 AM
Time and Components of Time: 0 0 73 12:01:13 AM HOUR : b/w 0 and 23
12:00:00 AM MINUTES and SECONDS : b/w 0 and 59
24 0 0 12:00:00 AM If HOUR greater than 23, then this value is divided by 24 and the remainder is used as the hour part of the returned time.
25 0 0 01:00:00 AM If MINUTE / SECOND is negative or greater than 59, then the time extends back or forward, into the previous or following hour.
1 -1 0 12:59:00 AM
1 0 0 01:00:00 AM
1 60 0 02:00:00 AM TIME HRS MINS SECS
1 1 -1 01:00:59 AM 8:23:02 8 23 2
1 1 0 01:01:00 AM 4:19:58 4 19 58
1 1 61 01:02:01 AM 26:26:26 2 26 26
FINANCIAL FUNCTIONS
LOAN AMOUNT 500000
INTEREST RATE 12.00%
PERIOD (in Years) 3
1. PMT

Annual Interest Rate 3.00%

2. IPMT & PPMT Period (in Years) 2
Number of Periods Per Year 4
Present Value 0
Future Value (Loan Amount) 500000
Payments are Due at the End
of Period 0

Future Value 140492.8 Present Value

3. PV & FV Interest 12%
Period (in Years) 3 Future Value

Amount(Quaterly) 1500 Rate

4. Rate & NPER Future Value 150000
Period (in Years) 15 Nper
 PERIOD PM (P + I)
Yearly ₹ 208,174.49
Half-Yearly ₹ 101,681.31
Quaterly ₹ 50,231.04
Monthly ₹ 16,607.15

QUARTERS PPMT (Principal) IPMT (Interest) INSTALLMENT BALANCE

1 ₹ 60,877.76 ₹ 3,750.00 ₹ 64,627.76 ₹ 435,372.24
2 ₹ 61,334.35 ₹ 3,293.42 ₹ 64,627.76 ₹ 374,494.48
3 ₹ 61,794.35 ₹ 2,833.41 ₹ 64,627.76 ₹ 313,160.13
4 ₹ 62,257.81 ₹ 2,369.95 ₹ 64,627.76 ₹ 251,365.78
5 ₹ 62,724.74 ₹ 1,903.02 ₹ 64,627.76 ₹ 189,107.97
6 ₹ 63,195.18 ₹ 1,432.58 ₹ 64,627.76 ₹ 126,383.22
7 ₹ 63,669.14 ₹ 958.62 ₹ 64,627.76 ₹ 63,188.04
8 ₹ 64,146.66 ₹ 481.10 ₹ 64,627.76 ₹ 0.00
TOTAL ₹ 500,000.00 ₹ 17,022.10

Present Value ₹ 100,000.00

Future Value ₹ 140,492.80

Quaterly Yearly
2% 7%

60 15
 CHART TYPES Amazon Summer Sales 2022
Column Chart In Store Sales Web Site Sales
Bar Chart Toys 22,150.00 43,690.00
Pie Chart Shoes 137,522.00 151,200.00
X-Y Scatter Chart Laptops 17,540.00 14,550.00
Sparklines Formatting

CHART ELEMENTS In Store Sales

Chart Area Toys 22,150.00
Plot Area Shoes 137,522.00
Series Laptops 17,540.00
Data Labels
Gridlines
Chart Title In Store Sales
160,000.00

140,000.00

120,000.00

100,000.00

Column Vs. Line/ 80,000.00

Combo Chart
60,000.00

40,000.00

20,000.00
Year Revenue Expenses
Quarter 1 100.00 50.00 0.00
Toys Shoes
Quarter 2 200.00 100.00
Quarter 3 300.00 150.00
Quarter 4 400.00 200.00

COMBO CHART Quarter Sales

450.00 Quarter 1 2,500.00
400.00 Quarter 2 1,000.00
350.00
300.00 Quarter 3 2,600.00
250.00 Quarter 4 6,000.00
200.00
150.00
100.00 Year Sales
50.00 2020 91,163
0.00
Quarter 1 Quarter 2 Quarter 3 Quarter 4 2021 134,599
Revenue Expenses
 150.00
100.00
50.00
0.00
Quarter 1 Quarter 2 Quarter 3 Quarter 4

Revenue Expenses
2022 148,414
2023 138,115
2024 199,845
2025 199,185

Bar Chart 2026 122,919

2027 180,820
2028 162,194

Sparkline Chart 2029

2030
249,388
196,892
2031 209,926
2032 355,488

Product Jan Feb Mar Product Region

Shoes 95 5 25 Chair East
Laptops 45 10 15 Table South
Chairs 55 15 75 Board West
Projectors 40 20 10 Marker West
Table 50 25 5 Laptop East

Column Projector MidWest

Line Switch South

Win / Loss Lights South

Camera MidWest
Pendrive South
Chair West

PIVOT TABLE
Table West
Board East
Marker South
Laptop West
Projector MidWest
Switch East
Lights West
Camera South
Pendrive South
Chair MidWest
Table East
Board West
Marker South
Laptop South
Projector West
Switch West
Lights East
Camera East
Pendrive MidWest
Chair South
Table West
er Sales 2022
AMAZON SUMMER SALES 2022
Mail Order Sales
57,890.00 160,000.00

43,500.00 140,000.00
27,890.00 120,000.00
100,000.00
80,000.00
60,000.00
40,000.00
20,000.00
0.00
In Store Sales Web Site Sales Mail Order Sales

Toys Shoes Laptops

In Store Sales

Column Chart

Sales

Shoes Laptops

Pie Chart

Quarter 1 Quarter 2 Quarter 3 Quarter 4

Sales
 Sales

0 50,000 100,000 150,000 200,000 250,000 300,000 350,000 400,000

2032 2031 2030 2029 2028 2027 2026 2025 2024 2023 2022 2021 2020

Name Units Sales COGS

John 16 200 232.99
Rita 24 250 240.35 Sum of Sales Region
Neeta 30 810.71 435.64 Name East MidWest
Jopseph 19 418.54 190.05 Harry
Robert 38 722.22 304.06 John 200 418.54

Nick 20 460.28 220.63 Jopseph

Reyan 23 483.58 213.57 Joseph

Harry 6 114.53 48.26 Luke 276.06 609.12

Luke 29 609.12 269.16 Neeta 1255.85
Simon 57 1197.97 527.98 Nick 2189.09
John 12 228.89 96.02 Reyan 276.06
Rita 60 1384.07 660.4 Rita 1197.97
Neeta 54 1026.96 432.75 Robert 722.22
Joseph 40 760.24 320.2 Simon 709.11
Robert 18 514.11 198.71 Total Result 3439.3 4414.72
Nick 64 1728.81 928.38
Reyan 12 276.06 123.56
Luke 18 709.11 465.4
Harry 23 345.89 325.06
Simon 21 119.53 210.05
Rita 47 1197.97 527.98
Neeta 32 228.89 96.02
Joseph 40 1384.07 660.4
Robert 64 1026.96 432.75
Nick 20 760.24 320.2
Reyan 38 514.11 198.71
Harry 72 1728.81 928.38
Luke 10 276.06 123.56
Simon 20 709.11 465.4
John 33 418.54 190.05
Rita 16 722.22 304.06
Neeta 19 460.28 220.63
 Mail Order Sales

art

Quarter 4
0,000 400,000

2 2021 2020

South West Total Result

460.42 1728.81 2189.23
228.89 847.43

418.54 418.54

760.24 1384.07 2144.31

709.11 1594.29
1270.99 2526.84
760.24 2949.33
483.58 514.11 1273.75
972.22 1384.07 3554.26
1026.96 514.11 2263.29
1317.5 2026.61
5781.16 8152.7 21787.88
 Jan Feb Mar Apr
A 40 25 40 55
B 20 29 38 47
C 30 33 36 39
D 40 37 34 31
E 50 41 32 23
F 60 45 30 15
G 70 49 28 7
 Jan Feb Mar Apr
A 10 15 20 25
B 15 20 22 16
C 18 25 30 20
D 30 16 10 30
E 45 44 30 45
F 50 26 25 15
G 22 55 15 30
 Jan Feb Mar Apr
A 25 10 35 10
B 10 20 25 25
C 30 25 40 35
D 15 15 30 20
E 40 30 10 25
F 50 40 15 60
G 10 45 20 10
 75 50 95 90 1. 3D SUM
45 69 85 88
78 83 106 94
85 68 74 81
135 115 72 93
160 111 70 90
102 149 63 47
1. 3D SUM
 Jan Feb Mar Apr
A 30 25 40 55
B 20 29 38 47
C 30 33 36 39
D 40 37 34 31
E 50 41 32 23
F 60 45 30 15
G 70 49 28 7
 Jan Feb Mar
A 10 15 20
B 15 20 22
C 18 25 30
D 30 16 10
E 45 44 30
F 50 26 25
G 22 55 15
Apr
25
16
20
30
45
15
30
 Jan Feb Mar Apr
A 25 10 35 10
B 10 20 25 25
C 30 25 40 35
D 15 15 30 20
E 40 30 10 25
F 50 40 15 60
G 10 45 20 10
 Jan Feb Mar Apr
A 75 50 95 90
B 45 69 85 88
C 78 83 106 94
D 85 68 74 81
E 135 115 72 93
F 160 111 70 90
G 102 149 63 47

Jan Feb Mar Apr

A 65 50 95 90
B 45 69 85 88
C 78 83 106 94
D 85 68 74 81
E 135 115 72 93
F 160 111 70 90
G 102 149 63 47
2. STATICALLSAME

3. LINKEDALLSAME