Question
Instructions: (The first column is A) 1. In F2, write a formula that will tell us if the sales associate meets the following criteria: if
Instructions: (The first column is A) 1. In F2, write a formula that will tell us if the sales associate meets the following criteria: if the associate has weekly expenses that are over $1000 and more than 40 clients, or they live in New Your City, indicate that there needs to be an increase in their expense account. Otherwise, leave the cell blank. Copy your formula from F2 to F3:F101
Use the ** expense status criteria to do the following problems
2. In G2, write a nested if formula to test the amount of weekly expenses of each associate and indicate the status of their expense account based off of the **expense status criteria. If none of the criteria are met, leave the cell blank. Copy your formula from G2 to G3:G101
3. In J2, select from the drop-down box the last name of oun of the associates whose last name starts with the same letter as your last name. You will notice that their weekly expense report will appear in J3. (here is that formula in J3: =IFERROR(VLOOKUP(J2,$A$2:$E$100,4,FALSE),"") )
4. In J4, write a VLOOKUP, where the range is TRUE (in other words, you are looking for an approximate match.) This VLOOKUP should use the criteria in **expense status criteria that was used in question 2. (hint: this is the table where you will find things that look like your weekly expenses number in J3.)
Any and all help is greatly appreciated!
Expense status Using IF(OR(AND Expense status (using nested IF) Expense Status (Using VLOOKUP where range is TRUE) Name (Select) Weekly Expenses: Expense Status: *Expense status criteria If the expenses are above $1000 and the number of cients is over 40 **Exponse status criteria Range Message $0 decrease $750 consider decrease $1,000 no change $ $1,500 consider increase $2.000 increase the location is New York City then recommend an increase in expense funds Otherwise, leave the cell blank Chicago first name location Tani New York City Kerry London Konstantine Alanla Clem Pittsburgh Jemima Chicago Dennie Londoni 6 Leigha London 9 Lily New York City 0 Maxle Chicago 1 Garey Atlanta 2 Christian Pittsburgh 3 Kari 3 Chicago 4 Kyslalle Aranta 5 Lenora Pittsburgh 6 Derrick London 7 Tristan New York City 8 Putnam Chicago 9 Emilia Allanla 0 Rodolphe Pittsburgh 21 Beatrice 22 Alis Londoni 23 Leroi New York City 24 Morty Chicago 25 Winslow Alanla 26 Lock Pittsburgh 27 Simone 28 Base New York City 29 Dreddy Chicago 20 Falecia Alanta 31 Banken Pittsburgh 32 Pamy London 33 Barbaraanne New York City 34 Joaquin Chicago 35 Constantia Atlanta 36 Eugenius Pittsburgh 37 Deina Chicago 38 Thor London 39 Jaymee New York City 40 Wendel Chicago 41 Northrup Alanta 42 Jimmy Pittsburgh 23 Zachorie London 44 Tait New York City 45 Petr 46 Evonne Atlanta 47 Carolann Pitisburgh Weekly expenses number of (in dolars) clients 1139.77 34 632.111 10 602.63 45 1268.45 34 1645.03 25 1531.53 35 1822 15 46 910 66 39 15413 15 17606 14 793.98 32 1008.52 47 447.04 33 315.02 18 1712.12 20 20 1466.42 41 B06.14 35 1824 97 42 1430 66 24 1927 44 47 1274.83 42 310 07 39 1446 5 36 1200 14 43 1743 37 26 1146 93 30 13087 50 1346 za 18 1791 1321.99 50 1996 25 19 77443 15 1169.59 18 1100.09 12 1327.02 10 1138.54 33 654.55 27 1834.38 26 1905.55 19 1103.36 49 1662.89 11 1321.51 12 494.01 33 898.43 33 1614.24 36 1184.51 13 London Chicago Expense status Using IF(OR(AND Expense status (using nested IF) Expense Status (Using VLOOKUP where range is TRUE) Name (Select) Weekly Expenses: Expense Status: *Expense status criteria If the expenses are above $1000 and the number of cients is over 40 **Exponse status criteria Range Message $0 decrease $750 consider decrease $1,000 no change $ $1,500 consider increase $2.000 increase the location is New York City then recommend an increase in expense funds Otherwise, leave the cell blank Chicago first name location Tani New York City Kerry London Konstantine Alanla Clem Pittsburgh Jemima Chicago Dennie Londoni 6 Leigha London 9 Lily New York City 0 Maxle Chicago 1 Garey Atlanta 2 Christian Pittsburgh 3 Kari 3 Chicago 4 Kyslalle Aranta 5 Lenora Pittsburgh 6 Derrick London 7 Tristan New York City 8 Putnam Chicago 9 Emilia Allanla 0 Rodolphe Pittsburgh 21 Beatrice 22 Alis Londoni 23 Leroi New York City 24 Morty Chicago 25 Winslow Alanla 26 Lock Pittsburgh 27 Simone 28 Base New York City 29 Dreddy Chicago 20 Falecia Alanta 31 Banken Pittsburgh 32 Pamy London 33 Barbaraanne New York City 34 Joaquin Chicago 35 Constantia Atlanta 36 Eugenius Pittsburgh 37 Deina Chicago 38 Thor London 39 Jaymee New York City 40 Wendel Chicago 41 Northrup Alanta 42 Jimmy Pittsburgh 23 Zachorie London 44 Tait New York City 45 Petr 46 Evonne Atlanta 47 Carolann Pitisburgh Weekly expenses number of (in dolars) clients 1139.77 34 632.111 10 602.63 45 1268.45 34 1645.03 25 1531.53 35 1822 15 46 910 66 39 15413 15 17606 14 793.98 32 1008.52 47 447.04 33 315.02 18 1712.12 20 20 1466.42 41 B06.14 35 1824 97 42 1430 66 24 1927 44 47 1274.83 42 310 07 39 1446 5 36 1200 14 43 1743 37 26 1146 93 30 13087 50 1346 za 18 1791 1321.99 50 1996 25 19 77443 15 1169.59 18 1100.09 12 1327.02 10 1138.54 33 654.55 27 1834.38 26 1905.55 19 1103.36 49 1662.89 11 1321.51 12 494.01 33 898.43 33 1614.24 36 1184.51 13 London ChicagoStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started