Formulas
Customer Invoice
Product Price Quantity Sub Tot Discount % Discount Amt Total
E-Tec USB 2 Drive $19.99 45 $899.55 5% 44.98 854.57
Lenolo Tab 2 $196.98 12 $2,363.76 10% 236.38 2127.38
Reflux A4 paper White 500 $24.99 28 $699.72 5% 34.99 664.73
Laser Jet Toner Cartridge Black $194.00 3 $582.00 5% 29.10 552.90
Totals 4199.59
Add +
Subtract -
Multiply *
Divide /
Exponent ^
Functions
Sales Ledger 2016
Name Q1 Q2 Q3 Q4 Total Average Max Min Commission Commission rate: 4%
Prentice 23100 13100 16800 37422 90422 22606 37422 13100 3616.88
Chirillo 22700 24110 48752 39566 135128 33782 48752 22700 5405.12 3.0000
Wong 20900 22600 20140 22185 85825 21456 22600 20140 3433 3.0000
Baker 15900 22700 17600 12334 68534 17134 22700 12334 2741.36 3.0000
Richards 25000 34000 21000 38443 118443 29611 38443 21000 4737.72 9
Hite 24110 54812 15200 28464 122586 30647 54812 15200 4903.44
de Moret 25600 48752 30300 18443 123095 30774 48752 18443 4923.8
McWilliams 22600 58445 16800 20454 118299 29575 58445 16800 4731.96
Howard 22700 48648 36855 29430 137633 34408 48648 22700 5505.32
Silvis 23300 24600 21380 42204 111484 27871 42204 21380 4459.36
Morris 23800 27700 12600 17790 81890 20473 27700 12600 3275.6
Lewis 54826 59224 24100 23667 161817 40454 59224 23667 6472.68
Jefferson 31200 23100 17700 34210 106210 26553 34210 17700 4248.4
Patrino 50224 17300 15200 27330 110054 27514 50224 15200 4402.16
Trowns-Hale 15500 19000 30300 34482 99282 24821 34482 15500 3971.28
Nguyen 30900 24400 16800 29843 101943 25486 30900 16800 4077.72
Johnsen 14815 13100 11580 15590 55085 13771 15590 11580 2203.4
OBrien 24500 25600 22000 38843 110943 27736 38843 22000 4437.72
Tantalo 20900 22600 20140 29466 93106 23277 29466 20140 3724.24
Parker 15900 22700 17600 39003 95203 23801 39003 15900 3808.12
TOTALS 508475 606491 432847 579169 2126982 XXXXXXXXXX
Summary Data
Summary Data 2016
Total Sales $2,126,982.00
Average Sales $26,587.50
Total Commission $85,079.28
Bonus
Bonus 5%
Emp ID First Name Surname Sales Bonus
1 Julie Fennell $ 120,000 $ 6,000
2 Sam Viney $ 70,000 $ 3,500
3 Andrea Ball $ 78,000 $ 3,900
4 Annabelle Lewis $ 45,000 $ 2,250
5 Cindy Thompson $ 89,000 $ 4,450
6 Andy Smith $ 88,000 $ 4,400
7 Fran Carpenter $ 80,000 $ 4,000
Named Ranges
Book Sales by Author
Author ID Contract Date Years Employed Books in Print Books Sold Sell Price Income Earned
1755 1/15/12 6.66 5 478985 2.99 XXXXXXXXXX Total Income Earned: XXXXXXXXXX
1932 1/7/12 6.68 12 274491 12.99 XXXXXXXXXX Total Books Sold: XXXXXXXXXX
1704 11/24/11 6.80 8 96684 9.99 XXXXXXXXXX Average Sell Price: XXXXXXXXXX
1328 10/10/11 6.93 5 66937 9.99 XXXXXXXXXX Average No Books in Print: XXXXXXXXXX
1048 7/19/11 7.15 4 43921 5.99 XXXXXXXXXX First Contract Date: 1/15/00
1818 5/5/11 7.36 8 192681 3.99 XXXXXXXXXX
1387 5/2/11 7.36 11 304729 2.99 XXXXXXXXXX
1325 3/9/11 7.51 6 102016 2.99 XXXXXXXXXX
1158 3/3/11 7.53 8 214442 10.99 XXXXXXXXXX
1747 3/2/11 7.53 5 70283 3.99 XXXXXXXXXX
1035 1/20/11 7.65 11 83399 10.99 XXXXXXXXXX
1142 12/21/10 7.73 1 225503 2.99 XXXXXXXXXX
1262 10/7/10 7.93 4 188565 2.99 XXXXXXXXXX
1206 9/16/10 7.99 1 213766 23.99 XXXXXXXXXX
1259 8/23/10 8.06 2 491853 12.99 XXXXXXXXXX
1676 6/22/10 8.23 6 222954 9.99 XXXXXXXXXX
1487 5/12/10 8.34 6 58989 12.99 XXXXXXXXXX
1006 5/5/10 8.36 15 316237 3.99 XXXXXXXXXX
1238 4/15/10 8.41 12 159593 3.99 XXXXXXXXXX
1162 1/17/10 8.66 1 295514 12.99 XXXXXXXXXX
1773 12/27/09 8.71 12 494065 7.99 XXXXXXXXXX
1939 12/27/09 8.71 8 283414 9.99 XXXXXXXXXX
1620 9/23/09 8.97 4 384719 7.99 XXXXXXXXXX
1372 5/28/09 9.29 13 326649 5.99 XXXXXXXXXX
1414 5/6/09 9.35 8 69750 3.99 XXXXXXXXXX
1928 4/27/09 9.38 1 230678 5.99 XXXXXXXXXX
1327 3/16/09 9.49 1 237358 9.99 XXXXXXXXXX
1929 12/3/08 9.78 9 9483 2.99 XXXXXXXXXX
1222 10/22/08 9.89 4 307741 5.99 XXXXXXXXXX
1871 10/1/08 9.95 8 454064 10.99 XXXXXXXXXX
1024 9/21/08 9.98 2 255650 7.99 XXXXXXXXXX
1428 6/29/08 10.21 9 16274 2.99 XXXXXXXXXX
1664 3/23/08 10.47 8 439448 5.99 XXXXXXXXXX
1950 3/2/08 10.53 5 133993 5.99 XXXXXXXXXX
1622 2/25/08 10.55 10 69105 3.99 XXXXXXXXXX
1179 2/14/08 10.58 11 43062 23.99 XXXXXXXXXX
1289 12/13/07 10.75 8 366628 23.99 XXXXXXXXXX
1514 10/24/07 10.89 14 341694 2.99 XXXXXXXXXX
1863 9/22/07 10.98 14 128447 12.99 XXXXXXXXXX
1482 8/7/07 11.10 9 459115 23.99 XXXXXXXXXX
1914 7/26/07 11.13 14 35987 2.99 XXXXXXXXXX
1911 4/29/07 11.37 11 14412 12.99 XXXXXXXXXX
1680 1/8/07 11.68 15 68935 2.99 XXXXXXXXXX
1246 12/24/06 11.72 1 269278 3.99 XXXXXXXXXX
1622 12/15/06 11.74 5 469732 9.99 XXXXXXXXXX
1513 10/4/06 11.94 15 170817 12.99 XXXXXXXXXX
1451 9/29/06 11.96 13 130656 10.99 XXXXXXXXXX
1534 9/28/06 11.96 15 20501 10.99 XXXXXXXXXX
1510 8/9/06 12.09 10 481840 10.99 XXXXXXXXXX
1803 7/1/06 12.20 13 113217 5.99 XXXXXXXXXX
1925 2/8/06 12.60 14 74256 7.99 XXXXXXXXXX
1817 12/22/05 12.73 7 494672 2.99 XXXXXXXXXX
1339 11/8/05 12.85 8 56188 10.99 XXXXXXXXXX
1045 6/24/05 13.22 7 283588 10.99 XXXXXXXXXX
1862 11/21/04 13.81 12 404443 12.99 XXXXXXXXXX
1376 10/24/04 13.89 6 55193 9.99 XXXXXXXXXX
1755 9/29/04 13.96 1 433029 15.99 XXXXXXXXXX
1021 8/6/04 14.10 14 344944 5.99 XXXXXXXXXX
1773 6/13/04 14.25 7 156940 2.99 XXXXXXXXXX
1326 6/11/04 14.26 12 142362 9.99 XXXXXXXXXX
1495 6/10/04 14.26 13 251418 9.99 XXXXXXXXXX
1390 11/27/03 14.79 1 202863 2.99 XXXXXXXXXX
1915 9/16/03 14.99 3 53061 2.99 XXXXXXXXXX
1647 6/22/03 15.23 9 357692 12.99 XXXXXXXXXX
1230 6/16/03 15.24 5 380640 7.99 XXXXXXXXXX
1958 6/8/03 15.26 12 490910 3.99 XXXXXXXXXX
1843 5/3/03 15.36 11 86193 15.99 XXXXXXXXXX
1804 3/30/03 15.45 14 53003 10.99 XXXXXXXXXX
1066 12/19/02 15.73 13 231823 3.99 XXXXXXXXXX
1951 9/26/02 15.96 13 292451 9.99 XXXXXXXXXX
1799 8/4/02 16.11 10 89636 7.99 XXXXXXXXXX
1210 6/1/02 16.28 12 365614 9.99 XXXXXXXXXX
1816 11/29/01 16.79 12 346024 2.99 XXXXXXXXXX
1902 10/18/01 16.90 9 424493 2.99 XXXXXXXXXX
1362 7/1/01 17.20 3 189282 10.99 XXXXXXXXXX
1701 6/23/01 17.22 12 207992 15.99 XXXXXXXXXX
1329 5/17/01 17.32 1 325510 7.99 XXXXXXXXXX
1042 4/2/01 17.45 15 16892 2.99 XXXXXXXXXX
1612 3/4/01 17.53 13 277507 15.99 XXXXXXXXXX
1416 12/27/00 17.71 11 493759 5.99 XXXXXXXXXX
1872 11/24/00 17.80 7 234903 2.99 XXXXXXXXXX
1111 11/8/00 17.85 11 140462 2.99 XXXXXXXXXX
1619 10/20/00 17.90 7 459194 15.99 XXXXXXXXXX
1198 9/28/00 17.96 1 496293 23.99 XXXXXXXXXX
1298 9/28/00 17.96 14 115011 2.99 XXXXXXXXXX
1699 8/1/00 18.12 1 329506 7.99 XXXXXXXXXX
1594 7/17/00 18.16 14 387860 12.99 XXXXXXXXXX
1546 6/14/00 18.25 7 339998 12.99 XXXXXXXXXX
1693 6/7/00 18.27 3 79116 5.99 XXXXXXXXXX
1185 4/29/00 18.37 3 197522 10.99 XXXXXXXXXX
1535 3/19/00 18.48 6 282843 7.99 XXXXXXXXXX
1952 2/13/00 18.58 8 194145 7.99 XXXXXXXXXX
1512 1/15/00 18.66 8 446184 2.99 XXXXXXXXXX
2222 6/30/18 0.20 5 555555 3.99 XXXXXXXXXX
Create Named Ranges using Name Box and Create from Selection
SUMIFS
First Name Surname Gender Department Salary
Julie Fennell F IT $120,000.00
Sam Viney M Marketing $70,000.00
Andrea Ball F Finance $78,000.00
Annabelle Lewis F IT $45,000.00
Cindy Thompson F Finance $89,000.00
Andy Smith M Finance $88,000.00
Fran Carpenter F Marketing $80,000.00
Number of Staff in Finance 3
Number of Staff who earn over 80K 3
Total Salaries in Finance $ 255,000
Total Salaries in IT $ 165,000
Total Salaries in Marketing $ 150,000
Challenge: Change the SUMIFS to get total salaries for FEMALES in each department
Finance IT Marketing 255000 165000 150000
IF
First Name Surname Attendance Results Grade 60 - 100 Pass
Julie Fennell 100 84 Pass 0 - 59 Fail
Sam Viney 65 57 Fail
Andrea Ball 80 68 Pass
Annabelle Lewis 80 71 Pass
Cindy Thompson 65 63 Pass
Andy Smith 100 75 Pass
Fred Carpenter 100 52 Fail
Logical Functions
=IF
=OR
=AND
IF KPI
Last First KPI Sales KPI Met Y/N Bonus Bonus Rates
DECKER Erica 100000 85736 2572.08 KPI Met 10%
BARRY Angela 120000 135647 Y 13564.7 KPI Not Met 3%
COLE Daniel 120000 61085 1832.55
CHAFFEE Adam 100000 137491 Y 13749.1
DONNELL Susan 80000 91386 Y 9138.6
FERN Elizabeth 120000 136424 Y 13642.4
ELLIS Janet 120000 154849 Y 15484.9
CHUNG Bob 120000 143610 Y 14361
FLANDERS Sabrina 100000 119338 Y 11933.8
CLARK John 100000 176077 Y 17607.7
BOLLER Mary 120000 120440 Y 12044
CAROL Elizabeth 150000 119887 3596.61
CLARK Anna 100000 91684 2750.52
CLARKE Joe 120000 136519 Y 13651.9
COMUNTZIS Mark 120000 147034 Y 14703.4
IF Invoice Data
Invoice # Invoice Amt Invoice Date Due Date Paid Date Days After Extension Early Discount Adjusted Amt
30245 $1,460.00 12/7/17 12/21/17 12/8/17 -13 146 $1,314.00
30246 $7,338.00 12/15/17 12/29/17 12/17/17 -12 733.8 $6,604.20
30247 $3,910.00 12/16/17 12/30/17 1/5/18 6 0 $3,910.00
30248 $7,045.00 12/24/17 1/7/18 1/5/18 -2 704.5 $6,340.50
30249 $4,733.00 12/24/17 1/7/18 1/25/18 18 0 $4,833.00
30250 $8,756.00 1/6/18 1/20/18 1/14/18 -6 875.6 $7,880.40
30251 $8,282.00 1/13/18 1/27/18 2/21/18 25 Y 0 $8,382.00
30252 $4,455.00 1/13/18 1/27/18 2/21/18 25 0 $4,555.00
30253 $816.00 1/17/18 1/31/18 2/6/18 6 0 $816.00
30254 $2,987.00 1/26/18 2/9/18 3/5/18 24 0 $3,087.00
30255 $8,717.00 1/26/18 2/9/18 2/18/18 9 0 $8,717.00
30256 $6,537.00 1/27/18 2/10/18 2/24/18 14 0 $6,537.00
30257 $4,758.00 1/30/18 2/13/18 3/5/18 20 Y 0 $4,858.00
30258 $558.00 1/31/18 2/14/18 2/21/18 7 0 $558.00
30259 $1,649.00 2/2/18 2/16/18 2/27/18 11 0 $1,649.00
Clients who pay before the due Date are elligible for a 10% discount
Clients who pay more than 14 days after the Due Date pay a $100 late penalty
Clients who have arranged for an extension are exempt from the penalty
Tables
Book Sales by Author Commission Rate: 3%
Author ID Contract Date Years Employed Books in Print Books Sold Sell Price Income Earned Commission
1006 5/5/10 1/8/00 15 316237 $ 3.90 XXXXXXXXXX XXXXXXXXXX
1021 8/6/04 1/14/00 14 344944 $ 5.99 XXXXXXXXXX XXXXXXXXXX
1024 9/21/08 1/9/00 2 255650 $ 7.99 XXXXXXXXXX XXXXXXXXXX
1035 1/20/11 1/7/00 11 83399 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1042 4/2/01 1/17/00 15 16892 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1045 6/24/05 1/13/00 7 283588 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1048 7/19/11 1/7/00 4 43921 $ 5.99 XXXXXXXXXX XXXXXXXXXX
1066 12/19/02 1/15/00 13 231823 $ 3.99 XXXXXXXXXX XXXXXXXXXX
1111 11/8/00 1/17/00 11 140462 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1142 12/21/10 1/7/00 1 225503 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1158 3/3/11 1/7/00 8 214442 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1162 1/17/10 1/8/00 1 295514 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1179 2/14/08 1/10/00 11 43062 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1185 4/29/00 1/18/00 3 197522 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1198 9/28/00 1/17/00 1 496293 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1206 9/16/10 1/7/00 1 213766 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1210 6/1/02 1/16/00 12 365614 $ 9.99 XXXXXXXXXX XXXXXXXXXX
1222 10/22/08 1/9/00 4 307741 $ 5.99 XXXXXXXXXX XXXXXXXXXX
1230 6/16/03 1/15/00 5 380640 $ 7.99 XXXXXXXXXX XXXXXXXXXX
1238 4/15/10 1/8/00 12 159593 $ 3.99 XXXXXXXXXX XXXXXXXXXX
1246 12/24/06 1/11/00 1 269278 $ 3.99 XXXXXXXXXX XXXXXXXXXX
1259 8/23/10 1/8/00 2 491853 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1262 10/7/10 1/7/00 4 188565 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1289 12/13/07 1/10/00 8 366628 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1298 9/28/00 1/17/00 14 115011 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1325 3/9/11 1/7/00 6 102016 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1326 6/11/04 1/14/00 12 142362 $ 9.99 XXXXXXXXXX XXXXXXXXXX
1327 3/16/09 1/9/00 1 237358 $ 9.99 XXXXXXXXXX XXXXXXXXXX
1328 10/10/11 1/6/00 5 66937 $ 9.99 XXXXXXXXXX XXXXXXXXXX
1329 5/17/01 1/17/00 1 325510 $ 7.99 XXXXXXXXXX XXXXXXXXXX
1339 11/8/05 1/12/00 8 56188 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1362 7/1/01 1/17/00 3 189282 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1372 5/28/09 1/9/00 13 326649 $ 5.99 XXXXXXXXXX XXXXXXXXXX
1376 10/24/04 1/13/00 6 55193 $ 9.99 XXXXXXXXXX XXXXXXXXXX
1387 5/2/11 1/7/00 11 304729 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1390 11/27/03 1/14/00 1 202863 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1414 5/6/09 1/9/00 8 69750 $ 3.99 XXXXXXXXXX XXXXXXXXXX
1416 12/27/00 1/17/00 11 493759 $ 5.99 XXXXXXXXXX XXXXXXXXXX
1428 6/29/08 1/10/00 9 16274 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1451 9/29/06 1/11/00 13 130656 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1482 8/7/07 1/11/00 9 459115 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1487 5/12/10 1/8/00 6 58989 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1495 6/10/04 1/14/00 13 251418 $ 9.99 XXXXXXXXXX XXXXXXXXXX
1510 8/9/06 1/12/00 10 481840 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1512 1/15/00 1/18/00 8 446184 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1513 10/4/06 1/11/00 15 170817 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1514 10/24/07 1/10/00 14 341694 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1534 9/28/06 1/11/00 15 20501 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1535 3/19/00 1/18/00 6 282843 $ 7.99 XXXXXXXXXX XXXXXXXXXX
1546 6/14/00 1/18/00 7 339998 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1594 7/17/00 1/18/00 14 387860 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1612 3/4/01 1/17/00 13 277507 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1619 10/20/00 1/17/00 7 459194 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1620 9/23/09 1/8/00 4 384719 $ 7.99 XXXXXXXXXX XXXXXXXXXX
1622 2/25/08 1/10/00 10 69105 $ 3.99 XXXXXXXXXX XXXXXXXXXX
1622 12/15/06 1/11/00 5 469732 $ 9.99 XXXXXXXXXX XXXXXXXXXX
1647 6/22/03 1/15/00 9 357692 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1664 3/23/08 1/10/00 8 439448 $ 5.99 XXXXXXXXXX XXXXXXXXXX
1676 6/22/10 1/8/00 6 222954 $ 9.99 XXXXXXXXXX XXXXXXXXXX
1680 1/8/07 1/11/00 15 68935 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1693 6/7/00 1/18/00 3 79116 $ 5.99 XXXXXXXXXX XXXXXXXXXX
1699 8/1/00 1/18/00 1 329506 $ 7.99 XXXXXXXXXX XXXXXXXXXX
1701 6/23/01 1/17/00 12 207992 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1704 11/24/11 1/6/00 8 96684 $ 9.99 XXXXXXXXXX XXXXXXXXXX
1747 3/2/11 1/7/00 5 70283 $ 3.99 XXXXXXXXXX XXXXXXXXXX
1755 1/15/12 1/6/00 5 478985 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1755 9/29/04 1/13/00 1 433029 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1773 6/13/04 1/14/00 7 156940 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1773 12/27/09 1/8/00 12 494065 $ 7.99 XXXXXXXXXX XXXXXXXXXX
1799 8/4/02 1/16/00 10 89636 $ 7.99 XXXXXXXXXX XXXXXXXXXX
1803 7/1/06 1/12/00 13 113217 $ 5.99 XXXXXXXXXX XXXXXXXXXX
1804 3/30/03 1/15/00 14 53003 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1816 11/29/01 1/16/00 12 346024 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1817 12/22/05 1/12/00 7 494672 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1818 5/5/11 1/7/00 8 192681 $ 3.99 XXXXXXXXXX XXXXXXXXXX
1843 5/3/03 1/15/00 11 86193 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1862 11/21/04 1/13/00 12 404443 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1863 9/22/07 1/10/00 14 128447 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1871 10/1/08 1/9/00 8 454064 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1872 11/24/00 1/17/00 7 234903 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1902 10/18/01 1/16/00 9 424493 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1911 4/29/07 1/11/00 11 14412 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1914 7/26/07 1/11/00 14 35987 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1915 9/16/03 1/14/00 3 53061 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1925 2/8/06 1/12/00 14 74256 $ 7.99 XXXXXXXXXX XXXXXXXXXX
1928 4/27/09 1/9/00 1 230678 $ 5.99 XXXXXXXXXX XXXXXXXXXX
1929 12/3/08 1/9/00 9 9483 $ 2.99 XXXXXXXXXX XXXXXXXXXX
1932 1/7/12 1/6/00 12 274491 $ XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX
1939 12/27/09 1/8/00 8 283414 $ 9.99 XXXXXXXXXX XXXXXXXXXX
1950 3/2/08 1/10/00 5 133993 $ 5.99 XXXXXXXXXX XXXXXXXXXX
1951 9/26/02 1/15/00 13 292451 $ 9.99 XXXXXXXXXX XXXXXXXXXX
1952 2/13/00 1/18/00 8 194145 $ 7.99 XXXXXXXXXX XXXXXXXXXX
1958 6/8/03 1/15/00 12 490910 $ 3.99 XXXXXXXXXX XXXXXXXXXX
prepared by Nicky 6/30/18
VLOOKUP Grade
Grades
Emp ID First Name Surname Results Grade
1 Julie Fennell 84 A
2 Sam Viney 57 D 0 Fail
3 Andrea Ball 68 C 40 E
4 Annabelle Lewis 71 B 50 D
5 Cindy Thompson 63 C 60 C
6 Andy Smith 75 B 70 B
7 Fran Carpenter 43 E 80 A
=VLOOKUP(Value,Data,Column,Range?)
VLOOKUP Product
Sales Record
Date Part Number Product Vendor Unit Price Units Total
6/20/13 1243 Keyboard Diamond $29.00 3 $87.00
6/20/13 1245 Monitor Pile $129.00 1 $129.00
6/20/13 1249 System Unit Treetop $199.00 2 $398.00
6/20/13 1271 Memory Board Pile $59.00 10 $590.00
TOTAL: $1,204.00
Product List
Part Number Product Vendor Units On-Hand Unit Price
1251 Mouse Ace 160 $19.00
1257 Keyboard Circular 48 $29.00
1269 Memory Board Circular 45 $59.00
1248 Hard Drive Circular 60 $99.00
1260 Hard Drive Circular 17 $99.00
1243 Keyboard Diamond 100 $29.00
1252 Monitor Diamond 40 $129.00
1264 Monitor Diamond 110 $129.00
1250 System Unit Diamond 100 $199.00
1256 System Unit Diamond 148 $199.00
1261 Mouse Letter C 32 $19.00
1270 Keyboard Letter C 10 $29.00
1247 CD ROM Drive Letter C 20 $39.00
1254 Hard Drive Letter C 40 $99.00
1265 Memory Board Nickel 89 $59.00
1272 System Unit Nickel 13 $199.00
1268 Mouse Pile 20 $19.00
1246 Memory Board Pile 300 $59.00
1253 Memory Board Pile 112 $59.00
1271 Memory Board Pile 2 $59.00
1273 Memory Board Pile 85 $59.00
1245 Monitor Pile 400 $129.00
1262 Monitor Pile 56 $129.00
1267 Monitor Pile 64 $129.00
1266 Mouse Quick Parts 35 $19.00
1258 Keyboard Quick Parts 36 $29.00
1263 CD ROM Drive Quick Parts 18 $39.00
1255 Monitor Quick Parts 43 $129.00
1244 Mouse Treetop 218 $19.00
1259 CD ROM Drive Treetop 5 $39.00
1249 System Unit Treetop 60 $199.00
Charts
Sales Summary
Year Book Sales Music Sales Total
2000 55085 67755 124840
2001 68534 93206 163741
2002 81890 113827 197719
2003 85825 113289 201117
2004 90422 120261 212687
2005 99282 129067 230354
2006 101943 139662 243611
2007 106210 135949 244166
2008 110054 161779 273841
2009 110943 145335 258287
2010 111484 150503 263997
2011 118299 145508 265818
2012 118443 145685 266140
2013 122586 166717 291316
2014 123095 155100 280209
2015 137633 198192 337840
2016 135128 189179 326323
2017 161817 233016 396850
Sales Summary
Book Sales 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 55085 68534 81890 85825 90422 99282 101943 106210 110054 110943 111484 118299 118443 122586 123095 137633 135128 161817 Music Sales 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 67755 93206 113827 113289 120261 129067 139662 135949 161779 145335 150503 145508 145685 166717 155100 198192 189179 233016