MICROSOFT EXCEL

Questions 9-17

I am absolutely clueless on how to correctly implement the functions they are asking for. Please help.

9	Next you want to calculate the total salaries for all Tax Accountants in Miami and San Diego. In cell P2, insert the DSUM function using the dataset, column heading Salary, and the defined criteria range.
10	Next, you will calculate the average salary for Tax Accountants in Miami and San Diego. In cell P3, insert the DAVERAGE function to calculate the average salary using the column heading Salary.
11	You want to identify the highest salary for Tax Accountants in Miami and San Diego. In cell P4, insert the DMAX function.
12	Next, you want to identify the lowest salary of Tax Accountants in Miami and San Diego. In cell P5, insert the DMIN function.
13	Finally, you want to count the number of Tax Accountants in Miami and San Diego. In cell P6, insert the DCOUNT function.
14	The range O8:P9 contains a new set of criteria to identify the one Senior Accountant in San Diego. You want to obtain that person's salary. In cell P11, insert the DGET function.
15	The range O13:P16 is designed to look up a person's name to return the position number and salary for that person. First, you will look up the person's location within the dataset. In cell P15, insert the MATCH function to look up the name in cell P14 and return that person's position within the Last Name column. Use only the range containing the actual last names in the argument.
16	Now that you have the position number for the accountant, you are ready to identify that person's salary. In cell P16, insert the INDEX function using the range F2:M26 as the range and the position number identified by the MATCH function.
17	In cell O19, insert the FORMULATEXT function to display the formula that is stored in cell P15. Copy the function to cell O20.

B C K N 0 P Q 10 $ 12 Total Salaries Average Salary High Salary Low Salary Number of TAS 13 14 15 16 Title Senior Accountant Location San Diego 17 1018 0218 1015 1214 0117 0417 0131 0303 0605 0616 0715 0918 0903 1106 1011 1108 0417 Sr. Accountant Salary D E 2007-1018-10 Camille 2008-0218-11 Ava 2009-1015-12 Isaac 2009-1214-13 Bradley 2011-0117-14 Oliver 2012-0417-15 Sophia 2014-0131-16 Audrey 2014-0303-17 Milia 2014-0605-18 Amanda 2014-0616-19 Torrie 2014-0715-20 Eilijah 2014-0918-21 Violet 2015-0903-22 Olivia 2015-1106-23 Melanie 2016-1011-24 Lucas 2016-1108-25 Dylan 2018-0417-26 Abia 2018-1104-27 Anthony 2019-0626-28 Ethan 2019-0830-29 Sebastian 2020-0108-30 Serenity 2020-0309-31 Jon 2020-0403-32 Liam 2020-0714-33 Zoey 2020-0813-34 Madison 2 2007 3 2008 4 2009 5 5 2009 6 6 2011 7 2012 8 2014 9 2014 10 2014 11 2014 12 2014 13 2014 14 2015 15 2015 16 2016 17 2016 18 2018 19 2018 20 2019 21 2019 22 2020 23 2020 24 2020 25 2020 26 2020 27 28 Year 29 30 31 32 Year 33 2008 34 2012 35 2014 36 2014 37 2014 38 2015 39 2015 40 2016 41 2016 42 2019 43 2020 44 F G H 1 J Adams Adams, Camille Senior Accountant $ 80,000 (305) 555-6105 305 Hartvigsen Hartvigsen, Ava Tax Accountant $ 73,000 (619) 555-0258 619 Terriquez Terriquez, Isaac Tax Accountant $ 71,750 (214) 555-8435 214 Deberard Deberard, Bradley Tax Accountant $ 70,795 (469) 555-6543 469 Laing Laing, Oliver Senior Accountant $ 80,500 (469) 555-2468 469 Lenz Lenz, Sophia Tax Accountant $ 79,750 (786) 555-2434 786 Unice Unice, Audrey Tax Accountant $ 68,750 (214) 555-1357 214 Omweg Omweg, Milia Senior Accountant $ 90,850 (518) 555-3145 518 Doering Doering, Amanda Tax Accountant $ 85,750 (518) 555-0470 518 Barnes Barnes, Torrie Tax Accountant $ 76,000 (858) 555-7418 858 Franklin Franklin, Eilijah Tax Accountant $ 77,240 (305) 555-9701 305 Garbett Garbett, Violet Tax Accountant $ 77,835 (305) 555-4464 305 Forgan Forgan, Olivia Tax Accountant $ 75,250 (786) 555-8016 786 Peterson Peterson, Melanie Tax Accountant $ 75,100 (786) 555-4567 786 Gomez Gomez, Lucas Tax Accountant $ 72,725 (619) 555-1023 619 Selinger Selinger, Dylan Tax Accountant $ 71,525 (619) 555-0430 619 lkenberry lkenberry, Abia Tax Accountant $ 82,125 (838) 555-7747 838 Bacarella Bacarella, Anthony Tax Accountant $ 67,575 (214) 555-4739 214 Mentzer Mentzer, Ethan Tax Accountant $ 70,450 (858) 555-3480 858 Mercado Mercado, Sebastian Tax Accountant $ 80,000 (838) 555-4433 838 Sae Sae, Serenity Tax Accountant $ 79,300 (838) 555-3435 838 Chen Chen, Jon Senior Accountant $ 85,800 (619) 555-6465 619 () Keone Keone, Liam Tax Accountant $ 66,125 (469) 555-5431 469 Scholfield Scholfield, Zoey Tax Accountant $ 70,750 (305) 555-1040 305 Nitz Nitz, Madison Tax Accountant $ 64,000 (469) 555-0918 469 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Lenz L M MIAMI Miami SAN DIEGO San Diego DALLAS Dallas DALLAS Dallas DALLAS Dallas MIAMI Miami DALLAS Dallas ALBANY Albany ALBANY Albany SAN DIEGO San Diego MIAMI Miami MIAMI Miami MIAMI Miami MIAMI Miami SAN DIEGO San Diego SAN DIEGO San Diego ALBANY Albany DALLAS Dallas SAN DIEGO San Diego ALBANY Albany ALBANY Albany SAN DIEGO San Diego DALLAS Dallas MIAMI Miami DALLAS Dallas Lookup Salary Enter last name Position of name Salary Functions 1104 0626 0830 0108 0309 0403 0714 33 0813 34 Date No. ID First Name Last Name Last, First Salary Phone Number Area Code Location Title Tax Accountant Tax Accountant City Miami San Diego Salary Date 0218 0417 0616 0715 0918 0903 1106 1011 1108 0626 0714 No. 11 15 19 20 21 22 23 24 25 28 33 ID First Name 2008-0218-11 Ava 2012-0417-15 Sophia 2014-0616-19 Torrie 2014-0715-20 Eilijah 2014-0918-21 Violet 2015-0903-22 Olivia 2015-1106-23 Melanie 2016-1011-24 Lucas 2016-1108-25 Dylan 2019-0626-28 Ethan 2020-0714-33 Zoey Last Name Last, First Title Hartvigsen Hartvigsen, Ava Tax Accountant Lenz Lenz, Sophia Tax Accountant Barnes Barnes, Torrie Tax Accountant Franklin Franklin, Eilijah Tax Accountant Garbett Garbett, Violet Tax Accountant Forgan Forgan, Olivia Tax Accountant Peterson Peterson, Melanie Tax Accountant Gomez Gomez, Lucas Tax Accountant Selinger Selinger, Dylan Tax Accountant Mentzer Mentzer, Ethan Tax Accountant Scholfield Scholfield, Zoey Tax Accountant Phone Number Area Code Location City $ 73,000 (619) 555-0258 619 SAN DIEGO San Diego $ 79,750 (786) 555-2434 786 MIAMI Miami $ 76,000 (858) 555-7418 858 SAN DIEGO San Diego $ 77,240 (305) 555-9701 305 MIAMI Miami $ 77,835 (305) 555-4464 305 MIAMI Miami $ 75,250 (786) 555-8016 786 MIAMI Miami $ 75,100 (786) 555-4567 786 MIAMI Miami $ 72,725 (619) 555-1023 619 SAN DIEGO San Diego $ 71,525 (619) 555-0430 619 SAN DIEGO San Diego $ 70,450 (858) 555-3480 858 SAN DIEGO San Diego $ 70,750 (305) 555-1040 305 MIAMI Miami 45 B C K N 0 P Q 10 $ 12 Total Salaries Average Salary High Salary Low Salary Number of TAS 13 14 15 16 Title Senior Accountant Location San Diego 17 1018 0218 1015 1214 0117 0417 0131 0303 0605 0616 0715 0918 0903 1106 1011 1108 0417 Sr. Accountant Salary D E 2007-1018-10 Camille 2008-0218-11 Ava 2009-1015-12 Isaac 2009-1214-13 Bradley 2011-0117-14 Oliver 2012-0417-15 Sophia 2014-0131-16 Audrey 2014-0303-17 Milia 2014-0605-18 Amanda 2014-0616-19 Torrie 2014-0715-20 Eilijah 2014-0918-21 Violet 2015-0903-22 Olivia 2015-1106-23 Melanie 2016-1011-24 Lucas 2016-1108-25 Dylan 2018-0417-26 Abia 2018-1104-27 Anthony 2019-0626-28 Ethan 2019-0830-29 Sebastian 2020-0108-30 Serenity 2020-0309-31 Jon 2020-0403-32 Liam 2020-0714-33 Zoey 2020-0813-34 Madison 2 2007 3 2008 4 2009 5 5 2009 6 6 2011 7 2012 8 2014 9 2014 10 2014 11 2014 12 2014 13 2014 14 2015 15 2015 16 2016 17 2016 18 2018 19 2018 20 2019 21 2019 22 2020 23 2020 24 2020 25 2020 26 2020 27 28 Year 29 30 31 32 Year 33 2008 34 2012 35 2014 36 2014 37 2014 38 2015 39 2015 40 2016 41 2016 42 2019 43 2020 44 F G H 1 J Adams Adams, Camille Senior Accountant $ 80,000 (305) 555-6105 305 Hartvigsen Hartvigsen, Ava Tax Accountant $ 73,000 (619) 555-0258 619 Terriquez Terriquez, Isaac Tax Accountant $ 71,750 (214) 555-8435 214 Deberard Deberard, Bradley Tax Accountant $ 70,795 (469) 555-6543 469 Laing Laing, Oliver Senior Accountant $ 80,500 (469) 555-2468 469 Lenz Lenz, Sophia Tax Accountant $ 79,750 (786) 555-2434 786 Unice Unice, Audrey Tax Accountant $ 68,750 (214) 555-1357 214 Omweg Omweg, Milia Senior Accountant $ 90,850 (518) 555-3145 518 Doering Doering, Amanda Tax Accountant $ 85,750 (518) 555-0470 518 Barnes Barnes, Torrie Tax Accountant $ 76,000 (858) 555-7418 858 Franklin Franklin, Eilijah Tax Accountant $ 77,240 (305) 555-9701 305 Garbett Garbett, Violet Tax Accountant $ 77,835 (305) 555-4464 305 Forgan Forgan, Olivia Tax Accountant $ 75,250 (786) 555-8016 786 Peterson Peterson, Melanie Tax Accountant $ 75,100 (786) 555-4567 786 Gomez Gomez, Lucas Tax Accountant $ 72,725 (619) 555-1023 619 Selinger Selinger, Dylan Tax Accountant $ 71,525 (619) 555-0430 619 lkenberry lkenberry, Abia Tax Accountant $ 82,125 (838) 555-7747 838 Bacarella Bacarella, Anthony Tax Accountant $ 67,575 (214) 555-4739 214 Mentzer Mentzer, Ethan Tax Accountant $ 70,450 (858) 555-3480 858 Mercado Mercado, Sebastian Tax Accountant $ 80,000 (838) 555-4433 838 Sae Sae, Serenity Tax Accountant $ 79,300 (838) 555-3435 838 Chen Chen, Jon Senior Accountant $ 85,800 (619) 555-6465 619 () Keone Keone, Liam Tax Accountant $ 66,125 (469) 555-5431 469 Scholfield Scholfield, Zoey Tax Accountant $ 70,750 (305) 555-1040 305 Nitz Nitz, Madison Tax Accountant $ 64,000 (469) 555-0918 469 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Lenz L M MIAMI Miami SAN DIEGO San Diego DALLAS Dallas DALLAS Dallas DALLAS Dallas MIAMI Miami DALLAS Dallas ALBANY Albany ALBANY Albany SAN DIEGO San Diego MIAMI Miami MIAMI Miami MIAMI Miami MIAMI Miami SAN DIEGO San Diego SAN DIEGO San Diego ALBANY Albany DALLAS Dallas SAN DIEGO San Diego ALBANY Albany ALBANY Albany SAN DIEGO San Diego DALLAS Dallas MIAMI Miami DALLAS Dallas Lookup Salary Enter last name Position of name Salary Functions 1104 0626 0830 0108 0309 0403 0714 33 0813 34 Date No. ID First Name Last Name Last, First Salary Phone Number Area Code Location Title Tax Accountant Tax Accountant City Miami San Diego Salary Date 0218 0417 0616 0715 0918 0903 1106 1011 1108 0626 0714 No. 11 15 19 20 21 22 23 24 25 28 33 ID First Name 2008-0218-11 Ava 2012-0417-15 Sophia 2014-0616-19 Torrie 2014-0715-20 Eilijah 2014-0918-21 Violet 2015-0903-22 Olivia 2015-1106-23 Melanie 2016-1011-24 Lucas 2016-1108-25 Dylan 2019-0626-28 Ethan 2020-0714-33 Zoey Last Name Last, First Title Hartvigsen Hartvigsen, Ava Tax Accountant Lenz Lenz, Sophia Tax Accountant Barnes Barnes, Torrie Tax Accountant Franklin Franklin, Eilijah Tax Accountant Garbett Garbett, Violet Tax Accountant Forgan Forgan, Olivia Tax Accountant Peterson Peterson, Melanie Tax Accountant Gomez Gomez, Lucas Tax Accountant Selinger Selinger, Dylan Tax Accountant Mentzer Mentzer, Ethan Tax Accountant Scholfield Scholfield, Zoey Tax Accountant Phone Number Area Code Location City $ 73,000 (619) 555-0258 619 SAN DIEGO San Diego $ 79,750 (786) 555-2434 786 MIAMI Miami $ 76,000 (858) 555-7418 858 SAN DIEGO San Diego $ 77,240 (305) 555-9701 305 MIAMI Miami $ 77,835 (305) 555-4464 305 MIAMI Miami $ 75,250 (786) 555-8016 786 MIAMI Miami $ 75,100 (786) 555-4567 786 MIAMI Miami $ 72,725 (619) 555-1023 619 SAN DIEGO San Diego $ 71,525 (619) 555-0430 619 SAN DIEGO San Diego $ 70,450 (858) 555-3480 858 SAN DIEGO San Diego $ 70,750 (305) 555-1040 305 MIAMI Miami 45