EXCEL ASSIGNMENT 4: FOUR SPREADSHEETS NEEDS COMPLETION, PLEASE SEE ATTACHED
Appy 7-1 Watson Security Systems Instructions Misy 5325 Project 4 Name: BennettAlycia IIII I I IIII IIII Do Not Do not insert or delete either rows or columns in any of these worksheets. Do not move any of the worksheets in this workbook relative to other worksheets. Do not modify the name and bar code in the grey bar of all worksheets. Do not change the order of the data if the instructions do not state that. Do Before submit your assignment, please rename the file by removing the work "blank" in front of the file name. For example, if the file titled "blankDoeJohnProj4" must be renamed to "DoeJohnProj4" Please note that the file name is case-sensitive (i.e., uppercase letter is NOT the same as lowercase letter) 5 % will be deducted for incorrect FileName, misorder of the worksheet, incorrect worksheet name, or incorrect information on the bar code Complete all of the worksheets. TableTotal Name: BennettAlycia IIII I I IIII IIII Instructions: Based on the data in the worksheet below, convert the data into "Table" and answer the questions below. Type answers into each cell with appropriate formats (need to be number, not text). No formulas in any cells. (1) How much has Daniel been paid so far? (2) What is the total payroll for all drivers so far? (3) How many miles has Martin driven so far? (4) How many miles has Travis driven so far? (5) How many hours has the big truck been driven so far? (6) What is the total payroll for hazardous material runs? Company NameNameHourly RatePhoneRun DateHaz MatBigTruckStart TimeEnd TimeStart OdomEnd OdomMilesHoursPayroll This and ThatMartin$15.00888-131502-Jan-98NoNo3:00:00 AM5:30:00 AM45555456741192.4999999999$37.50 NimBobMatthew$10.00650-551902-Jan-98NoNo3:45:00 AM12:15:00 PM43816442634478.4999999999$85.00 Manchesters FurnitureTravis$14.00650-288102-Jan-98NoYes5:45:00 AM9:15:00 AM47908480611533.5000000001$49.00 Micron ElectronicsKimberly$15.00650-238402-Jan-98NoYes11:30:00 AM4:00:00 PM46844470001564.5$67.50 NimBobTravis$14.00650-288102-Jan-98YesYes3:30:00 PM5:00:00 PM6257462623491.5$21.00 EligiosMatthew$10.00650-551903-Jan-98NoYes12:30:00 AM7:45:00 AM48243485733307.2500000001$72.50 Manchesters FurnitureDaniel$12.00992-443703-Jan-98NoYes10:00:00 AM11:45:00 AM5928559367821.7499999999$21.00 This and ThatKimberly$15.00650-238403-Jan-98NoNo2:45:00 PM11:00:00 PM58871592583878.25$123.75 GatewayErin$13.00756-838104-Jan-98NoYes4:45:00 AM1:45:00 PM61721621294089$117.00 EligiosCharles$16.00842-710805-Jan-98NoNo5:00:00 AM11:30:00 AM69184694242406.5000000001$104.00 Super CutsCharles$16.00855-569805-Jan-98NoNo6:15:00 AM3:15:00 PM41301415812809$144.00 Manchesters FurnitureErin$13.00756-838105-Jan-98NoNo11:30:00 AM7:45:00 PM48350486753258.25$107.25 Apple Corp.Erin$15.00855-475105-Jan-98YesNo12:00:00 PM10:45:00 PM524935287237910.7499999999$161.25 Jerry's PharmaceuticalsTricia$13.00756-286306-Jan-98NoYes5:45:00 AM3:15:00 PM62821634246039.5000000001$123.50 Manchesters FurnitureStaci$11.00756-811706-Jan-98NoNo6:15:00 AM8:00:00 AM6360063666661.7499999999$19.25 Manchesters FurnitureStaci$11.00756-811706-Jan-98YesNo7:45:00 AM11:45:00 AM43422435471253.9999999999$44.00 Micron ElectronicsJeanette$16.00992-548006-Jan-98NoNo10:45:00 AM7:00:00 PM51566520004348.25$132.00 EligiosJeanette$16.00992-548006-Jan-98YesYes5:30:00 PM2:15:00 AM46135465804458.7500000001$140.00 Micron ElectronicsTony$15.00994-738607-Jan-98NoYes5:30:00 AM2:45:00 PM62105624113069.2499999999$138.75 Micron ElectronicsStaci$11.00756-811707-Jan-98NoNo6:30:00 AM11:30:00 AM54294545282345.0000000001$55.00 Apple Corp.Erin$15.00678-456707-Jan-98NoNo12:00:00 PM8:15:00 PM54583548622798.25$123.75 EligiosTricia$13.00756-286307-Jan-98NoYes2:45:00 PM6:30:00 PM61414615181043.75$48.75 Micron ElectronicsCharles$16.00842-710807-Jan-98NoYes3:00:00 PM6:15:00 PM68492686591673.2499999999$52.00 EligiosLuke$10.00855-468607-Jan-98NoYes7:15:00 PM4:00:00 AM57967583964298.7500000001$87.50 Industrial Light and MagicMatthew$10.00650-551908-Jan-98NoNo11:45:00 AM5:45:00 PM40398407503526$60.00 EligiosErin$15.00678-456708-Jan-98NoYes12:30:00 PM7:00:00 PM62064624243606.5000000001$97.50 NimBobErin$14.00756-320108-Jan-98NoNo12:45:00 PM2:30:00 PM5844858524761.7499999999$24.50 EligiosTony$15.00994-738608-Jan-98NoYes12:45:00 PM9:15:00 PM60825612163918.4999999999$127.50 NimBobErin$15.00678-456708-Jan-98NoNo1:45:00 PM7:15:00 PM50455506541995.4999999999$82.50 GatewayTony$15.00994-738608-Jan-98NoYes2:15:00 PM11:45:00 PM67926683314059.5000000001$142.50 Jerry's PharmaceuticalsTricia$13.00756-286308-Jan-98YesYes6:00:00 PM3:15:00 AM48594489523589.2499999999$120.25 Manchesters FurnitureCharles$16.00842-710809-Jan-98YesNo4:15:00 AM10:15:00 AM69993702842916$96.00 EligiosJeanette$16.00992-548009-Jan-98NoYes10:15:00 AM3:45:00 PM68053683773245.4999999999$88.00 GatewayLuke$10.00855-468610-Jan-98NoYes4:30:00 AM2:30:00 PM43443439845419.9999999999$100.00 Jerry's PharmaceuticalsJeanette$16.00992-548010-Jan-98NoNo5:00:00 AM3:45:00 PM678796855567610.7499999999$172.00 Industrial Light and MagicStaci$11.00756-811710-Jan-98NoNo8:45:00 AM8:00:00 PM434604413767711.25$123.75 Super CutsDaniel$12.00992-443710-Jan-98NoNo11:30:00 AM9:30:00 PM43970444354659.9999999999$120.00 Jerry's PharmaceuticalsErin$14.00756-320110-Jan-98NoNo12:30:00 PM6:00:00 PM58746590613155.5000000001$77.00 This and ThatErin$16.00756-757810-Jan-98NoYes1:45:00 PM8:30:00 PM40375406622876.75$108.00 GatewayStaci$11.00756-811710-Jan-98NoNo6:15:00 PM8:00:00 PM4134741428811.7499999999$19.25 Apple Corp.Luke$10.00855-468613-Jan-98NoNo2:30:00 AM4:15:00 AM4320043261611.7499999999$17.50 EligiosTricia$13.00756-286314-Jan-98NoNo5:30:00 AM9:30:00 AM40067401781113.9999999999$52.00 Apple Corp.Daniel$12.00992-443714-Jan-98NoYes6:15:00 AM8:45:00 AM48113482441312.4999999999$30.00 Jerry's PharmaceuticalsErin$13.00756-838114-Jan-98NoNo6:30:00 AM9:00:00 AM4167141742712.4999999999$32.50 This and ThatMatthew$10.00650-551914-Jan-98YesNo9:30:00 AM12:15:00 PM48184483251412.7500000001$27.50 Manchesters FurnitureTravis$14.00650-288114-Jan-98NoNo12:15:00 PM8:30:00 PM48531488413108.25$115.50 This and ThatTricia$13.00756-286315-Jan-98YesNo12:30:00 AM8:45:00 AM53750539822328.25$107.25 Apple Corp.Martin$15.00888-131515-Jan-98YesNo6:15:00 AM3:00:00 PM67885681652808.7500000001$131.25 Super CutsCharles$16.00842-710815-Jan-98YesNo10:00:00 AM5:45:00 PM61705620233187.7499999999$124.00 Jerry's PharmaceuticalsTony$15.00994-738616-Jan-98NoNo4:45:00 AM11:45:00 AM52481527923116.9999999999$105.00 GatewayCharles$16.00855-569816-Jan-98NoNo5:00:00 AM8:45:00 AM6833068427973.75$60.00 GatewayCharles$16.00842-710816-Jan-98YesYes6:00:00 AM10:30:00 AM67479677512724.5$72.00 This and ThatStaci$11.00756-811716-Jan-98YesNo9:00:00 AM3:00:00 PM64291645933026$66.00 This and ThatMartin$15.00888-131516-Jan-98YesNo9:45:00 AM6:15:00 PM47732481153838.4999999999$127.50 EligiosDaniel$12.00992-443717-Jan-98YesNo7:15:00 AM4:45:00 PM59773602144419.5000000001$114.00 EligiosTony$15.00994-738618-Jan-98NoNo7:30:00 AM11:45:00 AM69344694561124.2500000001$63.75 Industrial Light and MagicShawn$14.00994-739318-Jan-98NoNo8:45:00 AM3:45:00 PM69330696152856.9999999999$98.00 This and ThatShawn$14.00994-739318-Jan-98NoNo6:30:00 PM5:30:00 AM499335037444111.0000000001$154.00 Manchesters FurnitureTricia$13.00756-286319-Jan-98NoYes4:30:00 AM6:15:00 AM6172661787611.7499999999$22.75 VLookUp EmployeeProductQuantitySale DateUnit PriceExtension/Sales AmountSales Discount Amount (in Dollar)Date Discount Amount (in Dollar)Discounted Price BobBanana23Monday JillOrange45Thursday BobApple65Wednesday GeorgeApple58Monday GeorgeBanana79Tuesday BobGrape132Wednesday JillGrape56Monday MikeKumQuat12Friday Totals: Unit Price VlookupSales/Extension Discount VlookupDate Discount Vlookup Name: BennettAlycia IIII I I IIII IIII Instructions: 1. Create 3 lookup tables based on the following information: Unit Price: Apple, $45; Banana, $34; Grape, $89; Kumquat,$150; Orange, $20 Sales Amount Discount: For the sales amount up to less than 1,000, the discount is 0%; for the sales amount from 1,000 up to less than 5,000, the discount is 2%; For the sales amount from 5,000 up to less than 10,000, the discount is 5%; for the sales amount 10,000 and up, the discount is 8%. Date Discount: Mon, 10%; Tue, 5%; Wed, 0%; Thu, 2%; Fri, 20% 2. Calculate Unit Price using VLOOKUP function 3. Calculate Extension/Sales Amount, which is equal to Unit Price X Quantity 4. Calculate Sales Amount Discount Amount using VLOOKUP function ((format cell to Currency) 5. Calculate Date Discount Amount using VLOOKUP function (format cell to Currency) 6. Calculate Discounted Price, which is equal to the Extension - (Sales Amount Discount)-(Date Discount Amount) 7. Calculate Total in cells F11 to I11 8. DO NOT change the order of the data from A1 to I12 Format cells in columns G, H, and I in this table to currency. DO NOT change the order of the data in this area (i.e., A1:I11). Doing so will cause a mis-match in grading. Apothecary Sales Rep List Name: BennettAlycia IIII I I IIII IIII Instructions: 1. Convert the data in Cells A12:J25 into Table 2. Add Total row to show the count of Gender column, the average in Age column, and sums in the Sales and Quota columns as shown above. 3. Use the Advanced Filter function to extract data with criteria (Region=3, Gender=M, and Quota>=3,000,000). Place the criteria under the Criteria Range section and place the results starting on row 30. Criteria Range Apothecary Sales Rep List RegionDivDistRepLnameFnameGenderAgeSalesQuota 2B2101KnightTonyaF312,167,3013,000,000 1A2108LiChangM254,544,0235,000,000 3B2120LawSamF584,893,0145,000,000 1B2207FreeLucyF323,718,2924,000,000 1B2208GreatJimM37989,4832,000,000 2A3210MayRodneyM232,565,9423,000,000 1B1212LempkePatM565,560,3456,000,000 3B2298PatelGandhiM292,109,5832,000,000 2B2299FennerJohnM323,693,2195,000,000 3A3312PennyJoshM322,210,4593,000,000 1A1313GeorgeDoreenF358,300,84510,000,000 3A1406LopesPedroM456,921,0325,000,000 3A1432WheatlyJaneF651,034,0542,000,000 Apothecary Sales Rep List Database Function Employee DataCriteria EmpLastnameEmpFirstNameEmpGenderEmpHireDateEmpBasePayEmpGender JonesKarenF11-Aug-91$2,000.00 PayneKathyF05-Dec-94$3,500.00 MorganAdrianM25-Oct-93$6,000.00 YuanGailF01-Mar-97$5,000.00Criteria PayneJustinM26-Apr-97$5,000.00EmpGender ClarksonShandraF30-May-97$3,000.00 BrionesRichardM28-Apr-98$4,500.00 ReetzMelindaF17-Oct-97$3,000.00 JonesWilliamM12-May-92$3,500.00Criteria GarzaJefferyM06-Jan-96$2,500.00EmpGenderEmpHireDate LucioTannerM29-Jun-93$2,500.00 BaldwinKristinaF21-Mar-98$2,000.00 NguyenJamesM09-Jul-95$2,000.00 BauerNeldaF08-Aug-98$3,000.00 KennemerMandiF07-Jul-99$2,000.00 Name: BennettAlycia IIII I I IIII IIII Instructions: Using the worksheet above to answer the following questions: (1) What is the total base pay for women? Using SUMIF function (2) What is the total of all base pay for men? Using SUMIF function (3) How many employee make $3500 basepay? Using CountIF function (4) What is the average basepay for women? Using DAVERAGE function (5) What is the total basepay for men? Using DSUM function (6) What is the total basepay for men hired before 1-jan-97? Using DSUM function (syntax for date is <01/01/1997) all cells must contain the database functions(i.e., typing the number directly into the cells will be graded as incorrect answers). this area is for queston 4 this area is for queston 5 this area is for queston 6 01/1997)="" all="" cells="" must="" contain="" the="" database="" functions(i.e.,="" typing="" the="" number="" directly="" into="" the="" cells="" will="" be="" graded="" as="" incorrect="" answers).="" this="" area="" is="" for="" queston="" 4="" this="" area="" is="" for="" queston="" 5="" this="" area="" is="" for="" queston="">