S.

no Name Tamil English Maths Science

1 AAA 35 61 78 89
2 BBB 64 23 87 100
3 CCC 56 23 99 12
4 DDD 86 45 12 97
5 EEE 39 36 23 92
6 FFF 69 35 35 98
7 GGG 35 77 44 81
8 HHH 58 70 78 87
9 III 40 23 57 85
10 JJJ 35 67 40 50

CELL REFERENCE
1. Relative
2. Absolute
3. Mixed
 price per
Social if if(and) product quantity
unit
56 Pass pass biscuit 40 30
89 Pass fail Chocolate 45 4
45 Pass fail Chair 500 2
51 Pass fail total
72 Pass fail
91 Pass pass
43 Pass pass
47 Pass pass
49 Pass fail
62 Pass pass

Science
 Product Amount Price with GST
18% 5%
Biscuit 50
Chocolate 80
Eraser 45
Pencil 35
Purse 80
Bag 500
Knife 250
Keychain 50
Pillow 700
Shirt 1200
Watch 3250

Amount
Bag 500
0 2 4 6 8 10
2 4 8 12 16 20
4 8 16 24 32 40
6 12 24 36 48 60
8 16 32 48 64 80
10 20 40 60 80 100
Product_ID Store Location Sales Revenue
402850 Boston 17 $ 2,499.00
436987 Boston 98 $ 784.00
764613 Boston 51 $ 6,732.00
243484 Chicago 32 $ 3,072.00
522010 Chicago 171 $ 25,650.00
346155 Chicago 51 $ 3,876.00
181763 Chicago 118 $ 10,030.00
410456 New York 52 $ 2,340.00
454175 New York 30 $ 3,960.00
426853 Boston 114 $ 4,446.00
815098 Boston 49 $ 5,537.00
209537 Boston 155 $ 21,390.00
765870 Boston 101 $ 3,535.00
747542 Chicago 149 $ 11,324.00

$ 105,175.00

Product_ID Store Location Sales Revenue

402850 Boston 17 $ 2,499.00
436987 Boston 98 $ 784.00
764613 Boston 51 $ 6,732.00
243484 Chicago 32 $ 3,072.00
522010 Chicago 171 $ 25,650.00
346155 Chicago 51 $ 3,876.00
181763 Chicago 118 $ 10,030.00
410456 New York 52 $ 2,340.00
454175 New York 30 $ 3,960.00
426853 Boston 114 $ 4,446.00
815098 Boston 49 $ 5,537.00
209537 Boston 155 $ 21,390.00
765870 Boston 101 $ 3,535.00
747542 Chicago 149 $ 11,324.00

Product_ID Store Location Sales Revenue

402850 Boston 17 $ 2,499.00
436987 Boston 98 $ 784.00
764613 Boston 51 $ 6,732.00
243484 Chicago 32 $ 3,072.00
522010 Chicago 171 $ 25,650.00
346155 Chicago 51 $ 3,876.00
181763 Chicago 118 $ 10,030.00
410456 New York 52 $ 2,340.00
454175 New York 30 $ 3,960.00
426853 Boston 114 $ 4,446.00
815098 Boston 49 $ 5,537.00
209537 Boston 155 $ 21,390.00
765870 Boston 101 $ 3,535.00
747542 Chicago 149 $ 11,324.00
Student Gender Score Pass/Fail Letter Outlier
Jill F 90 Pass A AVG
Tom M 80 Pass B AVG
Brittany F 96 Pass A OUTLIER
Alan M 72 Pass C AVG
George M 69 Pass D AVG
Sally F 52 Fail F OUTLIER
Chris M 99 Pass A OUTLIER
Jamie F 82 Pass B AVG
Valorie F 67 Pass D AVG
Steve M 90 Pass A AVG
Jake M 83 Pass B AVG
Lori F 89 Pass B AVG
Meghan F 60 Pass D AVG
Michelle F 63 Pass D AVG
Tim M 59 Fail F OUTLIER
 Award
None INSTRUCTIONS:
None
1) Populate column D to return "PASS" if the score in column C is
Female Achiever greater than or equal to 60, otherwise return "FAIL"
None
None 2) Populate column E to return a letter grade based on the score in
None column C, using the logic below:
Male Achiever A = >=90
None B = 80-89
None C = 70-79
D = 60-69
None F = <60
None
None 3) Populate column F to return "OUTLIER" if the score in column C is
either <60 or >90, otherwise return "AVG"
None
None 4) Populate column G to return "Male Achiever" if Gender = M and the
None score in column C is >95, "Female Achiever" if Gender = F and the
score in column C is >95, otherwise "None"
 SALES DATA EXAMPLE OF SUMI

EMPLOYEE
REGION ITEMS SOLD EMPLOYEE NAME
NAME
RAM EAST 100 SIVA
RAJA EAST 40 RAJA
RAM EAST 10
UDHAYA WEST 65

SIVA NORTH 69 EMPLOYEE NAME

ARUN SOUTH 45
ARUN EAST 85
RAM WEST 91
UDHAYA NORTH 63
RAJA EAST 58
SIVA NORTH 150
RAM SOUTH 67
ARUN EAST 79
KARTHICK SOUTH 85
UDHAYA SOUTH 84

ID NAME AREA
1 ANAND AVADI
2 RAJA AMBATTUR
3 SIVA AYYAPAKKAM
4 SHANKAR TAMBARAM
5 KARTHICK AVADI
6 UDHAYA AMBATTUR
7 RAHUL AYYAPAKKAM
8 BABU TAMBARAM
9 PRABHU AVADI
10 SARAVANAN AYYAPAKKAM
EXAMPLE OF SUMIFS

TOTAL ITEM
REGION
SOLD
North 109.5
EAST 2

TOTAL ITEM
REGION
SOLD

IF IF'S
SUMIF SUMIF'S
COUNTIF COUNTIF'S
AVERAGEIF AVERAGEIF'S
Order Item Amount
Red T-Shirt,
1001 750
BlueLarge
T-Shirt,
1002 500
RedSmall
T-Shirt,
1003 600
Medium
1004 Balck Shirt 450

1005 White Shirt 550

Grey T-Shirt,
1006 650
RedLarge
T-Shirt,
1007 350
BlueSmall
T-Shirt,
1008 475
BlueLarge
T-Shirt,
1009 675
Medium
Grey T-Shirt,
1010 235
Small
ID First_Name Last_Name Full_Name Random_Code Phrase
1 Karthik Rao Karthik Rao PHLS02 Hello World
2 Divya Nair Divya Nair BBB8CT Excel Functions
3 Ravi Babu Ravi Babu KUBAW8 Data Science
4 Ravi Singh Ravi Singh FN32KX ChatGPT AI
5 John Kumar John Kumar T35KN8 ChatGPT AI
6 Meena Kumar Meena Kumar PJECXH ChatGPT AI
7 Divya Babu Divya Babu CGQBZL ChatGPT AI
8 Arun Patel Arun Patel HGI1GA Data Science
9 Sneha Babu Sneha Babu W7LYMF Excel Functions
10 Meena Sharma Meena Sharma CPJT9P Excel Functions
11 Karthik Patel Karthik Patel SI35HF Hello World
12 Sara Singh Sara Singh TYHX1Q Excel Functions
13 Sneha Nair Sneha Nair YK126C Excel Functions
14 Ravi Sharma Ravi Sharma GZ7S99 Excel Functions
15 John Sharma John Sharma SO2I0Q Hello World
16 John Rao John Rao 975C9X ChatGPT AI
17 John Patel John Patel 9ZQMAA ChatGPT AI
18 Sneha Babu Sneha Babu JQ0MRY ChatGPT AI
19 Divya Babu Divya Babu PMENQB Data Science
20 Arun Singh Arun Singh 8WNW1T Python
21 Arun Nair Arun Nair L28DII Data Science
22 Arun Singh Arun Singh 0F7MIR Data Science
23 Sara Rao Sara Rao N8D24L Python
24 John Singh John Singh K1TXCP Excel Functions
25 Meena Rao Meena Rao 7AYOM5 Data Science
26 Arun Singh Arun Singh 30IBA7 Data Science
27 Ravi Singh Ravi Singh CFA2RI Python
28 Karthik Nair Karthik Nair 2V0536 Data Science
29 Ravi Singh Ravi Singh 89DH2O Excel Functions
30 Ravi Babu Ravi Babu EHE9JW Excel Functions
31 Karthik Kumar Karthik Kumar 269OQU Excel Functions
32 Karthik Babu Karthik Babu FB2V1C ChatGPT AI
33 Meena Sharma Meena Sharma P5ZR0V Hello World
34 Arun Kumar Arun Kumar 5MG1NW Data Science
35 Divya Babu Divya Babu VAD0ZA Python
36 Divya Singh Divya Singh 9LFNEB Data Science
37 Ravi Patel Ravi Patel SBWMFV Data Science
38 Ravi Rao Ravi Rao 8ZP6WI Excel Functions
39 Sneha Babu Sneha Babu SPDLIM Hello World
40 Ravi Singh Ravi Singh 6RJHEW Data Science
41 John Sharma John Sharma OGKBC0 ChatGPT AI
42 Divya Nair Divya Nair LM7DWJ Excel Functions
43 Divya Nair Divya Nair 7NRQWQ ChatGPT AI
44 Ravi Babu Ravi Babu TJBMB7 Hello World
45 Sara Sharma Sara Sharma CEWBNO Hello World
46 Meena Singh Meena Singh XMKN7G Data Science
47 Sneha Singh Sneha Singh 1065D0 Hello World
48 Sneha Patel Sneha Patel URZ04R ChatGPT AI
49 Arun Kumar Arun Kumar V17WJT Python
50 Sneha Rao Sneha Rao A0226M Hello World
51 Karthik Singh Karthik Singh 822DLN Data Science
52 Meena Singh Meena Singh GVQN9P Hello World
53 Divya Singh Divya Singh SH4JM4 Excel Functions
54 Karthik Babu Karthik Babu P22WDL Excel Functions
55 Divya Babu Divya Babu 14YSZA Excel Functions
56 Karthik Patel Karthik Patel LRSMC1 ChatGPT AI
57 Divya Nair Divya Nair 9X5XI2 Data Science
58 Sneha Sharma Sneha Sharma P0F55C Hello World
59 John Rao John Rao HW8OJ6 Data Science
60 Karthik Babu Karthik Babu G4NA5F Python
61 Karthik Patel Karthik Patel 33YUFR ChatGPT AI
62 Meena Nair Meena Nair VC40L9 Python
63 Divya Nair Divya Nair 90DEGE Data Science
64 Ravi Nair Ravi Nair BL32VH Python
65 Sara Kumar Sara Kumar 7OEG74 Python
66 Arun Singh Arun Singh WM6LU5 Data Science
67 Divya Sharma Divya Sharma V4B8MZ Python
68 Sneha Rao Sneha Rao 4YLCDO Data Science
69 Ravi Kumar Ravi Kumar 4LHD40 Hello World
70 Divya Nair Divya Nair 4OFFM1 ChatGPT AI
71 Sara Rao Sara Rao CJ4J31 ChatGPT AI
72 John Patel John Patel JTDX3R ChatGPT AI
73 Sara Babu Sara Babu GDEHM9 ChatGPT AI
74 Divya Rao Divya Rao 011GVV Hello World
75 Arun Babu Arun Babu 75E5HJ Data Science
76 Karthik Singh Karthik Singh ZB6NZ9 ChatGPT AI
77 Arun Singh Arun Singh 4ZSLGX Excel Functions
78 Sara Patel Sara Patel SNMW4M Hello World
79 Ravi Singh Ravi Singh 0A4TZ6 Data Science
80 Sneha Singh Sneha Singh QTOQKJ ChatGPT AI
81 Divya Rao Divya Rao B31BVG Python
82 Arun Nair Arun Nair J8L0VJ Hello World
83 Sara Kumar Sara Kumar 54BH55 Hello World
84 Sneha Kumar Sneha Kumar UKJOAZ Excel Functions
85 Arun Kumar Arun Kumar QDPGLL ChatGPT AI
86 Sneha Singh Sneha Singh 5A95H9 Python
87 Karthik Kumar Karthik Kumar 3JDLA5 Python
88 Sneha Patel Sneha Patel GHYHJ8 Excel Functions
89 Meena Rao Meena Rao 7P2RIM Excel Functions
90 Karthik Patel Karthik Patel JQ5ZIZ ChatGPT AI
91 Ravi Singh Ravi Singh VV9V5Z Excel Functions
92 Arun Patel Arun Patel WGJN5H Excel Functions
93 Sneha Nair Sneha Nair 4HVKR2 ChatGPT AI
94 Divya Patel Divya Patel KN4GVA ChatGPT AI
95 Sneha Babu Sneha Babu CFCX7S Python
 96 Sneha Singh Sneha Singh 7UBSIK ChatGPT AI
97 Ravi Rao Ravi Rao X0PV2Z ChatGPT AI
98 Divya Rao Divya Rao HU5N56 Excel Functions
99 Arun Rao Arun Rao 0VYTIJ Excel Functions
100 Divya Babu Divya Babu H6KREE Data Science
 42

TEXT FUNCTION

Left Kart Ravi

Right Rao Nair
Middle Karthik Karthik
Len 11 9
Trim Hello World Hello World 11
Concat KarthikRao ArunPatel
Proper Excuse Me Hello World
Upper UPPER HELLO WORLD
Find 3 3
Replace Divya Welcome WORLD
Employee Office Department 2015 Sales 2016 Sales
Kathy BOS Electronics $18,270 $22,873
John BOS Electronics $20,510 $21,279
Mindy BOS Apparel $15,972 $13,096
Aaron BOS Apparel $17,347 $10,990
Phil NYC Electronics $19,635 $10,283
Tom NYC Electronics $13,190 $15,332
Bill NYC Electronics $17,585 $15,414
Lauren NYC Apparel $19,588 $21,973
Jason NYC Apparel $15,769 $18,721
Mark NYC Apparel $25,347 $25,897
Kim CHI Electronics $29,120 $21,824 1) Calculate the maximum 2016 Sales (c
Tina CHI Electronics $19,325 $26,697
2) Calculate the maximum 2016 Sales in
Jean CHI Electronics $23,810 $28,543
Sam CHI Apparel $26,508 $28,682 3) Return the total number of rows whe
Ashley CHI Apparel $17,872 $17,631
4) Calculate the SUM of 2015 and 2016 s
 Max 2016 Sales in NYC office: $25,897

Max 2016 Sales in NYC office, Electronics only: $15,414

# of rows where 2016 Sales > 2015 Sales: 9

Sum of 2015 and 2016 Sales in Boston: $140,337

INSTRUCTIONS:

1) Calculate the maximum 2016 Sales (column E) in the New York office.
2) Calculate the maximum 2016 Sales in the New York office, for the Electronics department only.

3) Return the total number of rows where 2016 Sales are greater than 2015 Sales.

4) Calculate the SUM of 2015 and 2016 sales for the Boston office.
 Product Category Product ID Store Location Sales Revenue
Apparel & Accessories 402850 Boston 17 $2,499
Apparel & Accessories 436987 Boston 98 $784
Apparel & Accessories 764613 Boston 51 $6,732
Apparel & Accessories 243484 Chicago 32 $3,072
Apparel & Accessories 522010 Chicago 171 $25,650
Apparel & Accessories 346155 Chicago 51 $3,876
Apparel & Accessories 181763 Chicago 118 $10,030
Apparel & Accessories 410456 New York 52 $2,340
Apparel & Accessories 454175 New York 30 $3,960
Consumer Electronics 426853 Boston 114 $4,446
Consumer Electronics 815098 Boston 49 $5,537
Consumer Electronics 209537 Boston 155 $21,390
Consumer Electronics 765870 Boston 101 $3,535
Consumer Electronics 747542 Chicago 149 $11,324
Consumer Electronics 177975 Chicago 119 $17,850
Consumer Electronics 840614 New York 97 $3,298
Consumer Electronics 271572 New York 127 $17,907
Consumer Electronics 367240 New York 104 $12,584
Consumer Electronics 791819 New York 91 $9,282
Health & Beauty 185858 Boston 51 $765
Health & Beauty 501837 Boston 142 $14,484
Health & Beauty 486900 Boston 161 $11,592
Health & Beauty 207266 Boston 100 $1,100
Health & Beauty 328524 Boston 187 $16,269
Health & Beauty 430342 Chicago 147 $13,524
Health & Beauty 602517 Chicago 107 $3,317
Health & Beauty 183106 Chicago 170 $22,780
Health & Beauty 843050 Chicago 192 $20,160
Health & Beauty 129851 Chicago 134 $14,070
Health & Beauty 829456 Chicago 98 $490
Health & Beauty 832520 Chicago 138 $14,076
Health & Beauty 142498 New York 111 $7,548
Health & Beauty 690368 New York 118 $590
Health & Beauty 267419 New York 127 $5,969
Health & Beauty 140721 New York 69 $9,384
Health & Beauty 454504 New York 44 $2,464
Health & Beauty 343862 New York 38 $190
Food & Beverage 491299 Boston 35 $4,865
Food & Beverage 547421 Boston 71 $3,266
Food & Beverage 594052 Boston 180 $22,860
Food & Beverage 720964 Boston 189 $25,137
Food & Beverage 104556 Boston 92 $9,752
Food & Beverage 202419 Boston 152 $14,136
Food & Beverage 281646 Boston 102 $10,098
Food & Beverage 153152 Boston 199 $3,383
Food & Beverage 273563 Boston 60 $2,640
Food & Beverage 679548 Chicago 72 $10,152
Food & Beverage 509361 Chicago 136 $3,264
Food & Beverage 431791 Chicago 114 $16,302
Food & Beverage 776118 Chicago 66 $3,300
Food & Beverage 401681 Chicago 102 $7,548
Food & Beverage 821745 Chicago 79 $1,580
Food & Beverage 202386 Chicago 132 $12,276
Food & Beverage 104375 Chicago 39 $312
Food & Beverage 235860 Chicago 145 $1,160
Food & Beverage 477156 Chicago 48 $1,680
Food & Beverage 320691 Chicago 11 $209
Food & Beverage 558332 Chicago 115 $1,955
Food & Beverage 156812 Chicago 152 $8,968
Food & Beverage 824637 Chicago 86 $5,418
Food & Beverage 167040 Chicago 66 $7,194
Food & Beverage 803330 Chicago 116 $8,468
Food & Beverage 250827 Chicago 89 $8,010
Food & Beverage 605290 New York 119 $2,142
Food & Beverage 312187 New York 28 $4,116
Food & Beverage 303581 New York 93 $1,860
Food & Beverage 735076 New York 114 $6,270
Food & Beverage 467396 New York 127 $6,350
Food & Beverage 465336 New York 196 $20,776
Product Category: Consumer Electronics

Total Sales
1,106

Total Sales Total Revenue

Boston 419 $34,908
Chicago 268 $29,174
New York 419 $43,071

INSTRUCTIONS:

1) calculates the Total Sales based on the product category ?

2) Populate Total Sales and Total Revenue in the table above and calculate Number of Product IDs ?
 Apparel & Accessories
Consumer Electronics
Health & Beauty
Food & Beverage

Number of Product IDs

4
2
4

CTIONS:

egory ?

above and calculate Number of Product IDs ?

 Database Functions

Pear Company - Q1 Expenses

Division Category January February March Total Expenses
East Technical Support $ 800.00 $ 650.00 $ 700.00 $ 2,150.00
East Telephone $ 900.00 $ 850.00 $ 850.00 $ 2,600.00
East Copying $ 4,850.00 $ 3,200.00 $ 1,155.00 $ 9,205.00
East Overhead $ 1,250.00 $ 1,250.00 $ 1,250.00 $ 3,750.00
East Software $ 2,025.00 $ 2,200.00 $ 1,650.00 $ 5,875.00
East Maintenance $ 1,350.00 $ 1,500.00 $ 1,700.00 $ 4,550.00
East Supplies $ 3,300.00 $ 3,500.00 $ 3,700.00 $ 10,500.00
East Telemarketing $ 3,825.00 $ 3,725.00 $ 3,750.00 $ 11,300.00
East Contractors $ 8,900.00 $ 10,315.00 $ 5,250.00 $ 24,465.00
East Consultants $ 6,250.00 $ 6,000.00 $ 6,500.00 $ 18,750.00
East Rent $ 8,000.00 $ 8,000.00 $ 8,000.00 $ 24,000.00
East Miscellaneous $ 11,500.00 $ 12,500.00 $ 12,500.00 $ 36,500.00
East Advertising $ 12,250.00 $ 12,250.00 $ 12,750.00 $ 37,250.00
East Clerical Support $ 25,000.00 $ 24,000.00 $ 26,390.00 $ 75,390.00
North Technical Support $ 800.00 $ 950.00 $ 750.00 $ 2,500.00
North Overhead $ 850.00 $ 750.00 $ 800.00 $ 2,400.00
North Maintenance $ 940.00 $ 950.00 $ 820.00 $ 2,710.00
North Telephone $ 980.00 $ 850.00 $ 950.00 $ 2,780.00
North Contractors $ 1,250.00 $ 1,250.00 $ 1,250.00 $ 3,750.00
North Software $ 1,150.00 $ 1,255.00 $ 1,400.00 $ 3,805.00
North Supplies $ 2,410.00 $ 1,850.00 $ 2,390.00 $ 6,650.00
North Telemarketing $ 3,200.00 $ 3,760.00 $ 3,750.00 $ 10,710.00
North Copying $ 5,000.00 $ 4,800.00 $ 4,500.00 $ 14,300.00
North Consultants $ 5,250.00 $ 8,990.00 $ 5,515.00 $ 19,755.00
North Rent $ 6,020.00 $ 6,020.00 $ 6,020.00 $ 18,060.00
North Miscellaneous $ 12,940.00 $ 11,300.00 $ 11,500.00 $ 35,740.00
North Advertising $ 14,250.00 $ 15,250.00 $ 12,050.00 $ 41,550.00
North Clerical Support $ 25,700.00 $ 24,200.00 $ 26,930.00 $ 76,830.00
South Overhead $ 2,140.00 $ 2,310.00 $ 2,000.00 $ 6,450.00
South Technical Support $ 730.00 $ 525.00 $ 430.00 $ 1,685.00
South Telephone $ 700.00 $ 750.00 $ 750.00 $ 2,200.00
South Maintenance $ 2,000.00 $ 950.00 $ 800.00 $ 3,750.00
South Supplies $ 745.00 $ 780.00 $ 900.00 $ 2,425.00
South Software $ 1,150.00 $ 1,200.00 $ 1,400.00 $ 3,750.00
South Copying $ 2,780.00 $ 3,590.00 $ 2,300.00 $ 8,670.00
South Contractors $ 3,490.00 $ 32,840.00 $ 3,070.00 $ 39,400.00
South Rent $ 4,700.00 $ 4,700.00 $ 4,700.00 $ 14,100.00
South Consultants $ 5,250.00 $ 5,000.00 $ 5,500.00 $ 15,750.00
South Telemarketing $ 6,980.00 $ 6,310.00 $ 6,375.00 $ 19,665.00
South Advertising $ 11,250.00 $ 11,250.00 $ 11,750.00 $ 34,250.00

Page 24
 Database Functions

South Miscellaneous $ 24,500.00 $ 23,500.00 $ 24,500.00 $ 72,500.00

South Salaries $ 56,900.00 $ 62,800.00 $ 60,870.00 $ 180,570.00
South Clerical Support $ 24,290.00 $ 24,050.00 $ 26,600.00 $ 74,940.00
West Overhead $ 775.00 $ 750.00 $ 700.00 $ 2,225.00
West Telephone $ 700.00 $ 750.00 $ 750.00 $ 2,200.00
West Technical Support $ 300.00 $ 100.00 $ 150.00 $ 550.00
West Supplies $ 2,000.00 $ 1,800.00 $ 1,900.00 $ 5,700.00
West Maintenance $ 2,000.00 $ 950.00 $ 800.00 $ 3,750.00
West Contractors $ 1,250.00 $ 1,250.00 $ 1,250.00 $ 3,750.00
West Software $ 1,150.00 $ 1,200.00 $ 1,435.00 $ 3,785.00
West Telemarketing $ 3,800.00 $ 3,700.00 $ 3,750.00 $ 11,250.00
West Copying $ 5,000.00 $ 4,800.00 $ 4,545.00 $ 14,345.00
West Rent $ 5,000.00 $ 5,000.00 $ 5,000.00 $ 15,000.00
West Consultants $ 5,250.00 $ 5,335.00 $ 5,500.00 $ 16,085.00
West Advertising $ 10,250.00 $ 10,250.00 $ 10,750.00 $ 31,250.00
West Miscellaneous $ 14,500.00 $ 13,500.00 $ 15,500.00 $ 43,500.00
West Salaries $ 72,000.00 $ 70,000.00 $ 70,000.00 $ 212,000.00
West Clerical Support $ 25,000.00 $ 24,000.00 $ 26,000.00 $ 75,000.00

Page 25
 Database Functions

Look up
Expenses

Division Category Total Expenses

East Software $5,875.00

Look up
TOTAL Expenses

Category Total Expense

Software $17,215.00

Look up
AVG. Expenses

Category AVG. Expense

Rent $17,790.00

Page 26
 Date Revenue Revenue Change Cost Profit
1/1/2014 $1,099 $275 $643 $456
2/1/2014 $1,204 $105 $720 $211
3/1/2014 $1,944 $740 $964 $980
4/1/2014 $1,743 -$201 $830 $913
5/1/2014 $1,609 -$134 $910 $699
6/1/2014 $1,494 -$115 $909 $585
7/1/2014 $1,959 $465 $830 $1,129
8/1/2014 $1,868 -$91 $906 $962
9/1/2014 $1,162 -$706 $606 $556
10/1/2014 $1,424 $262 $943 $481
11/1/2014 $1,232 -$192 $801 $431
12/1/2014 $1,738 $506 $786 $952
1/1/2015 $1,435 -$303 $575 $860
2/1/2015 $1,865 $430 $754 $1,111
3/1/2015 $1,234 -$631 $599 $635
4/1/2015 $1,577 $343 $940 $637
5/1/2015 $1,983 $406 $954 $1,029
6/1/2015 $1,356 -$627 $577 $779
7/1/2015 $1,874 $518 $865 $883
8/1/2015 $1,479 -$395 $588 $891
9/1/2015 $1,943 $464 $785 $712
10/1/2015 $1,444 -$499 $657 $787
11/1/2015 $1,493 $49 $880 $613
12/1/2015 $1,738 $245 $580 $1,158
Top Product Category Profit Margin
Apparel 41.5%
INSTRUCTIONS:
Electronics 17.5%
Kids 50.4% 1) Format the Revenue column as currency, with no decimal places, and
Apparel 52.4% to apply the same formatting to columns C, D, and E
Electronics 43.4% 2) Format the Profit Margin column as a percent, with 1 decimal place
Apparel 39.2%
Electronics 57.6% 3) Use conditional formatting to add a color scale to the Profit Margin
Apparel 51.5% high values = green)
Kids 47.8% 4) Use conditional formatting to add directional arrow icons to the Rev
Kids 33.8% edit to show an up arrow for values >200 and a down arrow for values
Electronics 35.0%
5) Create a formula-based formatting rule to format the dates in colum
Apparel 54.8% text and light red fill when the profit margin in column G is <40%
Electronics 59.9%
Electronics 59.6% 6) Add a formula-based formatting rule to highlight rows in columns A-
Apparel 51.5% Top Product Category = "Kids"
Electronics 40.4%
Apparel 51.9%
Kids 57.4%
Electronics 47.1%
Apparel 60.2%
Electronics 36.6%
Apparel 54.5%
Kids 41.1%
Electronics 66.6%
 INSTRUCTIONS:

ncy, with no decimal places, and use the format painter

ns C, D, and E

a percent, with 1 decimal place

color scale to the Profit Margin column (low values = red,

rectional arrow icons to the Revenue Change column, and

00 and a down arrow for values <-200
ule to format the dates in column A as bold with dark red
argin in column G is <40%

e to highlight rows in columns A-F light yellow when the

 Date Functions
Today() 12/17/2025
now() 12/17/2025 4:52
date() 12/6/2025
day() 17
month() 12
year() 2025
weekday() 5
weeknum() 51
Eomonth() 2/28/2026
Networkdays() 47
Edate() 2/17/2026

Time Functions
Time() 11:45 AM
Hour() 11
Minutes() 45
Second() 34
Now() 12/17/2025 4:52
Timevalue() 04:30:00 PM
ons

ons
Product Category Sum of Sales
Apparel & Accessories 620
Consumer Electronics 1106
Food & Beverage 3325
Sum of Sales by Product Ca
Health & Beauty 2134
Health & Beauty 2134

This shape represents a slicer. Food & Beverage 3325

Slicers are supported in Excel

category
2010 or later.
Consumer Electronics 1106
If the shape was modified in
an earlier version of Excel, or Apparel & Accessories 620
if the workbook was saved in
Excel 2003 or earlier, the
0 500 1000 1500 200
slicer cannot be used.
Revenue
es by Product Category
34

25

06

500 1000 1500 2000 2500 3000 3500

Revenue
Store Location Sum of Revenue Total
Chicago $258,015 $600,000
New York $117,030
Boston $185,270 $500,000
Total Result $560,315
$400,000
Sum of Revenue
$300,000

$200,000

$100,000

$0
Chicago New York Boston Total Result
 Sum of Revenue

Result
 Product Category Product ID Store Location Sales Revenue

Apparel & Accessories 402850 Boston 17 $2,499

Apparel & Accessories 436987 Boston 98 $784
Apparel & Accessories 764613 Boston 51 $6,732
Apparel & Accessories 243484 Chicago 32 $3,072
Apparel & Accessories 522010 Chicago 171 $25,650
Apparel & Accessories 346155 Chicago 51 $3,876
Apparel & Accessories 181763 Chicago 118 $10,030
Apparel & Accessories 410456 New York 52 $2,340
Apparel & Accessories 454175 New York 30 $3,960
Consumer Electronics 426853 Boston 114 $4,446
Consumer Electronics 815098 Boston 49 $5,537
Consumer Electronics 209537 Boston 155 $21,390
Consumer Electronics 765870 Boston 101 $3,535
Consumer Electronics 747542 Chicago 149 $11,324
Consumer Electronics 177975 Chicago 119 $17,850
Consumer Electronics 840614 New York 97 $3,298
Consumer Electronics 271572 New York 127 $17,907
Consumer Electronics 367240 New York 104 $12,584
Consumer Electronics 791819 New York 91 $9,282
Health & Beauty 185858 Boston 51 $765
Health & Beauty 501837 Boston 142 $14,484
Health & Beauty 486900 Boston 161 $11,592
Health & Beauty 207266 Boston 100 $1,100
Health & Beauty 328524 Boston 187 $16,269
Health & Beauty 430342 Chicago 147 $13,524
Health & Beauty 602517 Chicago 107 $3,317
Health & Beauty 183106 Chicago 170 $22,780
Health & Beauty 843050 Chicago 192 $20,160
Health & Beauty 129851 Chicago 134 $14,070
Health & Beauty 829456 Chicago 98 $490
Health & Beauty 832520 Chicago 138 $14,076
Health & Beauty 142498 New York 111 $7,548
Health & Beauty 690368 New York 118 $590
Health & Beauty 267419 New York 127 $5,969
Health & Beauty 140721 New York 69 $9,384
Health & Beauty 454504 New York 44 $2,464 30000

Health & Beauty 343862 New York 38 $190 20000

10000
New York
New York

New York
New York
New York

Food & Beverage 491299 Boston 35 $4,865

Chicago
Chicago
Chicago
Chicago

Chicago
Chicago
Boston
Boston
Boston

Boston
Boston
Boston
Boston

Food & Beverage 547421 Boston 71 $3,266

Food & Beverage 594052 Boston 180 $22,860 4472531444827718237
0364248152106474769
Food & Beverage 720964 Boston 189 $25,137 2643261046595770171
8964017418058596528
5818156575937471741
Food & Beverage 104556 Boston 92 $9,752 0734053653870254209
AAAAAAAAACCCCCCCCCC
Food & Beverage 202419 Boston 152 $14,136 pppppppppoooooooooo
pppppppppnnnnnnnnnn
aaaaaaaaa s s s s s s s s s s
Food & Beverage 281646 Boston 102 $10,098 r r r r r r r r r uuuuuuuuuu
e e e e e e e e e mmmmmmmmmm
l l l l l l l l l eeeeeeeeee
Food & Beverage 153152 Boston 199 $3,383 &&&&&&&&&r r r r r r r r r r
EEEEEEEEEE
AAAAAAAAA l l l l l l l l l l
c c c c c c c c c eeeeeeeeee
ccccccccccccccccccc
eeeeeeeee t t t t t t t t t t
sssssssss r r r r r r r r r r
s s s s s s s s s oooooooooo
ooooooooonnnnnnnnnn
rrrrrrrrri i i i i i i i i i
i i i i i i i i i cccccccccc
eeeeeeeee s s s s s s s s s s
sssssssss
 0734053653870254209
AAAAAAAAACCCCCCCCCC
pppppppppoooooooooo
pppppppppnnnnnnnnnn
aaaaaaaaa s s s s s s s s s s
r r r r r r r r r uuuuuuuuuu
e e e e e e e e e mmmmmmmmmm
l l l l l l l l l eeeeeeeeee
&&&&&&&&&r r r r r r r r r r
EEEEEEEEEE
AAAAAAAAA l l l l l l l l l l
Food & Beverage 273563 Boston 60 $2,640 c c c c c c c c c eeeeeeeeee
ccccccccccccccccccc
Food & Beverage 679548 Chicago 72 $10,152 eeeeeeeee t t t t t t t t t t
sssssssss r r r r r r r r r r
s s s s s s s s s oooooooooo
Food & Beverage 509361 Chicago 136 $3,264 ooooooooonnnnnnnnnn
rrrrrrrrri i i i i i i i i i
Food & Beverage 431791 Chicago 114 $16,302 i i i i i i i i i cccccccccc
eeeeeeeee s s s s s s s s s s
sssssssss
Food & Beverage 776118 Chicago 66 $3,300
Food & Beverage 401681 Chicago 102 $7,548
Food & Beverage 821745 Chicago 79 $1,580
Food & Beverage 202386 Chicago 132 $12,276
Food & Beverage 104375 Chicago 39 $312
$30,000
Food & Beverage 235860 Chicago 145 $1,160
Food & Beverage 477156 Chicago 48 $1,680 $25,000
Food & Beverage 320691 Chicago 11 $209
Food & Beverage 558332 Chicago 115 $1,955 $20,000
Food & Beverage 156812 Chicago 152 $8,968
$15,000
Food & Beverage 824637 Chicago 86 $5,418
Food & Beverage 167040 Chicago 66 $7,194 $10,000
Food & Beverage 803330 Chicago 116 $8,468
$5,000
Food & Beverage 250827 Chicago 89 $8,010
Food & Beverage 605290 New York 119 $2,142 $0
Food & Beverage 312187 New York 28 $4,116 on go go on on
st ca ca st st Yo
Food & Beverage 303581 New York 93 $1,860 Bo Chi Chi Bo Bo ew e
N N
Food & Beverage 735076 New York 114 $6,270
Food & Beverage 467396 New York 127 $6,350
Food & Beverage 465336 New York 196 $20,776
 1 Create a Pivot Table to find the total Sales by Store Location.

2 Create a Pivot Table to find the total Revenue by Product Category.

3 Show average Sales for each Product Category.

4 Display count of Product IDs per Store Location.

5 Find total Revenue for each Store Location and Product Category in a 2-D Pivot Table.

Data
Product Category Sum of Revenue Sum of Sales
Apparel & Accessories $58,943 620
Consumer Electronics $107,153 1106
Food & Beverage $235,447 3325
Health & Beauty $158,772 2134

Chart Title
30000
20000
10000
New York
New York

New York
New York
New York
New York

New York
New York
New York
New York
New York
New York

New York
New York
New York
New York
New York
New York
Chicago
Chicago
Chicago
Chicago

Chicago
Chicago

Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago

Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Chicago
Boston
Boston
Boston

Boston
Boston
Boston
Boston

Boston
Boston
Boston
Boston
Boston

Boston
Boston
Boston
Boston
Boston
Boston
Boston
Boston
Boston

447253144482771823715423461818816214345571221265474821243518182633744
036424815210647476980802308422349645494920085770370200372552605010366
264326104659577017151678023399220704317404213399161124570864730523575
896401741805859652888925351084543475824095461553716733816386038215033
581815657593747174153062410555296120692565145646918487659313432988793
073405365387025420987064276016088914291246962381181565061227007071666
AAAAAAAAACCCCCCCCCCHHHHHHHHHHHHHHHHHHF F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F
pppppppppooooooooooe e e e e e e e e e e e e e e e e e oooooooooooooooooooooooooooooooo
pppppppppnnnnnnnnnna a a a a a a a a a a a a a a a a a oooooooooooooooooooooooooooooooo
a a a a a a a a a s s s s s s s s s s l l l l l Column
l l l l l l lDl l l lColumn
l l ddddE
dddddddddddddddddddddddddddd
r r r r r r r r r uuuuuuuuuu t t t t t t t t t t t t t t t t t t
e e e e e e e e e mmmmmmmmmmhhhhhhhhhhhhhhhhhh&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
l l l l l l l l l eeeeeeeeee
&&&&&&&&&r r r r r r r r r r &&&&&&&&&&&&&&&&&&BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
EEEEEEEEEE eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
AAAAAAAAA l l l l l l l l l l BBBBBBBBBBBBBBBBBB v v v v v v v v v v v v v v v v v v v v v v v v v v v v v v v v
c c c c c c c c c eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
c c c c c c c c c c c c c c c c c c c aaaaaaaaaaaaaaaaaa r r r r r r r r r r r r r r r r r r r r r r r r r r r r r r r r
e e e e e e e e e t t t t t t t t t t uuuuuuuuuuuuuuuuuua a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a
s s s s s s s s s r r r r r r r r r r t t t t t t t t t t t t t t t t t t gggggggggggggggggggggggggggggggg
s s s s s s s s s oooooooooo y y y y y y y y y y y y y y y y y y e e e e e e e e e e e e e e e e e e e e e e e e e e e e e e e e
ooooooooonnnnnnnnnn
rrrrrrrrri i i i i i i i i i
i i i i i i i i i cccccccccc
eeeeeeeee s s s s s s s s s s
sssssssss
 073405365387025420987064276016088914291246962381181565061227007071666
AAAAAAAAACCCCCCCCCCHHHHHHHHHHHHHHHHHHF F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F F
pppppppppooooooooooe e e e e e e e e e e e e e e e e e oooooooooooooooooooooooooooooooo
pppppppppnnnnnnnnnna a a a a a a a a a a a a a a a a a oooooooooooooooooooooooooooooooo
a a a a a a a a a s s s s s s s s s s l l l l l Column
l l l l l l lDl l l lColumn
l l ddddE
dddddddddddddddddddddddddddd
r r r r r r r r r uuuuuuuuuu t t t t t t t t t t t t t t t t t t
e e e e e e e e e mmmmmmmmmmhhhhhhhhhhhhhhhhhh&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
l l l l l l l l l eeeeeeeeee
&&&&&&&&&r r r r r r r r r r &&&&&&&&&&&&&&&&&&BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
EEEEEEEEEE eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
AAAAAAAAA l l l l l l l l l l BBBBBBBBBBBBBBBBBB v v v v v v v v v v v v v v v v v v v v v v v v v v v v v v v v
c c c c c c c c c eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
c c c c c c c c c c c c c c c c c c c aaaaaaaaaaaaaaaaaa r r r r r r r r r r r r r r r r r r r r r r r r r r r r r r r r
e e e e e e e e e t t t t t t t t t t uuuuuuuuuuuuuuuuuua a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a a
s s s s s s s s s r r r r r r r r r r t t t t t t t t t t t t t t t t t t gggggggggggggggggggggggggggggggg
s s s s s s s s s oooooooooo y y y y y y y y y y y y y y y y y y e e e e e e e e e e e e e e e e e e e e e e e e e e e e e e e e
ooooooooonnnnnnnnnn
rrrrrrrrri i i i i i i i i i
i i i i i i i i i cccccccccc
eeeeeeeee s s s s s s s s s s
sssssssss

Chart Title
$30,000

$25,000

$20,000

$15,000

$10,000

$5,000

$0
on go go on on rk rk on go go go rk rk on on on go go go go go rk rk
st ca ca st st Yo Yo st ca ca ca Yo Yo st st st ca ca ca ca ca Yo Yo
Bo Chi Chi Bo Bo ew ew Bo Chi Chi Chi ew ew Bo Bo Bo Chi Chi Chi Chi Chi ew ew
N N N N N N
tegory in a 2-D Pivot Table.