EX16XL CH07 GRADER CAP AS Sales Data 1.3 Step Perform an advanced fiher based on the criterla range. Set the fiter to copy the new data to he range A22:622 In cel 17, enter a database function to cakulate the rumber of reps meeting the crtteria. Use 7 the output range 22:G3S for the database argument in the function. Project Description: Your assistant created a spreadsheet that lists names, hire dates, quarterlhy sales, and total sales for 2018. You will calculate the number of years each representative has worked and each representative's bonus. In addition, you will create a nested lookup function to look quarter to identify the sales for that person. Next, you will perform advanced database filtering and use summary database functions. Finally, you wll com- plete an amortization table to build a small addition to your office complex In ct 1 ]8, terter a :latabase function to alculate the highest lutal sales for records meeting the criterid. Use th1output range A22: Gas for the database aruument in the function. In cel 19, enter a database funtion to cakulate the aveage sales for records meeting the criteria. Use the ouput ange A22:G35 ehe dalabase agument in the funutien. 7.000 up a person's name and particular ick the Addihan worksheet tab, and than Incert a formula in celF tn caldate the lan acunt hased cn the loan parameters. In cel E5, enter a function to caoulate the monthly payment. Modity the function to ensure hat the resut is a pesitive number 5.000 Instructions: For the purpose of grading the project you are required to perform the follow- ing tasks In cel E6, enter a function to cdoulate the total intret paid after frve payments.Modfy the function to ensure that the result is the loan amortzaton table completed. 12 pDatrve number. The formulu will reuut in an error until In cel B11, uuate redabve reference to cel B/ dnd inI C11, uuate ciel E2. In cel C12, enter a relatwe referenceto ce!11 and copy reabve reference tu formula to the range 4.000 Step Downibad and open the file named exgeoaade awes.nsy and then save the In cel 012, insert a nested DATE function to enter the payment date tor the next mnth e a exploring_e07_grader_a1_Sales_LastFirst, replacing Lasost with your name. 0.000 14 Copy the function to the range B13 015. on the Sales worksheet, enter a date function in cell CO to calculate the number ef years the in cnl D11, enter the financial functon to calculate the nterest paid for the first pay mart period. The result should be a pnsme vabR st representative has worked for your company. Ccpy the function to the range C9:C20 15 On the Sales wrksheel, ter a nested funt un in cdIJ9 cf the Borus cclumn to display the bns amount. Lf the smployee sok $200,000 ur more (cell 12) AND is Intsnational (cdl 4) he or she earns a 5% (ceD4) bonus on his or her total annual sales; otherwise, the representative earns 3% (cel ]3) of his or her total annual sales. Use relate and moed or absolute refer tricts correctly n the neted function. Copy the function from jg to the range In cel Ell, enter the finandal fundtion to caloulate the prindpal payment for the first payment period. The result should be a posiive value 16 7.000 In cel FI1, subtract the principal payment In cell Ell from the begining balance In cell CLL Copy the functions and fomulas from the range D11:Fll to the range D12:F15, Enter a nested luokup functin in ell E4 that uses the tels E2 and E3 tu reum a speifit sale:s record. For exemple, ueing the ourt data, you want the function to reburn the third quarte sales for Ernca. Save the fle meking sure the worksheets are in Uhe fulowing order: Seles, Dalabase, a Addition. Cu Excel. Submit the fl a directed 19 0.000 Click the Database orksheet t and enter conditions in the Crite a Range for Domestic sales reps that made 240000 r mere in sales 455 Total Paints 10.D EX16XL CH07 GRADER CAP AS Sales Data 1.3 Step Perform an advanced fiher based on the criterla range. Set the fiter to copy the new data to he range A22:622 In cel 17, enter a database function to cakulate the rumber of reps meeting the crtteria. Use 7 the output range 22:G3S for the database argument in the function. Project Description: Your assistant created a spreadsheet that lists names, hire dates, quarterlhy sales, and total sales for 2018. You will calculate the number of years each representative has worked and each representative's bonus. In addition, you will create a nested lookup function to look quarter to identify the sales for that person. Next, you will perform advanced database filtering and use summary database functions. Finally, you wll com- plete an amortization table to build a small addition to your office complex In ct 1 ]8, terter a :latabase function to alculate the highest lutal sales for records meeting the criterid. Use th1output range A22: Gas for the database aruument in the function. In cel 19, enter a database funtion to cakulate the aveage sales for records meeting the criteria. Use the ouput ange A22:G35 ehe dalabase agument in the funutien. 7.000 up a person's name and particular ick the Addihan worksheet tab, and than Incert a formula in celF tn caldate the lan acunt hased cn the loan parameters. In cel E5, enter a function to caoulate the monthly payment. Modity the function to ensure hat the resut is a pesitive number 5.000 Instructions: For the purpose of grading the project you are required to perform the follow- ing tasks In cel E6, enter a function to cdoulate the total intret paid after frve payments.Modfy the function to ensure that the result is the loan amortzaton table completed. 12 pDatrve number. The formulu will reuut in an error until In cel B11, uuate redabve reference to cel B/ dnd inI C11, uuate ciel E2. In cel C12, enter a relatwe referenceto ce!11 and copy reabve reference tu formula to the range 4.000 Step Downibad and open the file named exgeoaade awes.nsy and then save the In cel 012, insert a nested DATE function to enter the payment date tor the next mnth e a exploring_e07_grader_a1_Sales_LastFirst, replacing Lasost with your name. 0.000 14 Copy the function to the range B13 015. on the Sales worksheet, enter a date function in cell CO to calculate the number ef years the in cnl D11, enter the financial functon to calculate the nterest paid for the first pay mart period. The result should be a pnsme vabR st representative has worked for your company. Ccpy the function to the range C9:C20 15 On the Sales wrksheel, ter a nested funt un in cdIJ9 cf the Borus cclumn to display the bns amount. Lf the smployee sok $200,000 ur more (cell 12) AND is Intsnational (cdl 4) he or she earns a 5% (ceD4) bonus on his or her total annual sales; otherwise, the representative earns 3% (cel ]3) of his or her total annual sales. Use relate and moed or absolute refer tricts correctly n the neted function. Copy the function from jg to the range In cel Ell, enter the finandal fundtion to caloulate the prindpal payment for the first payment period. The result should be a posiive value 16 7.000 In cel FI1, subtract the principal payment In cell Ell from the begining balance In cell CLL Copy the functions and fomulas from the range D11:Fll to the range D12:F15, Enter a nested luokup functin in ell E4 that uses the tels E2 and E3 tu reum a speifit sale:s record. For exemple, ueing the ourt data, you want the function to reburn the third quarte sales for Ernca. Save the fle meking sure the worksheets are in Uhe fulowing order: Seles, Dalabase, a Addition. Cu Excel. Submit the fl a directed 19 0.000 Click the Database orksheet t and enter conditions in the Crite a Range for Domestic sales reps that made 240000 r mere in sales 455 Total Paints 10.D
