Top management decided to ensure all representatives' salaries are at least $62,000 (cell G2).
In cell L9, nest an AND function within an IF function. If the job title is Representative and the salary is less than the minimum representative salary, calculate the difference between the minimum representative salary and the actual salary. If not, return zero. Use a mixed reference to cell G3. Copy the function from cell L9 to the range L10:L33. 

Transcribed Image Text:

27 Kimura 23 lim Tuesday Representative Popresentative Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Calibri (Body) v 11 v A A ab Wrap Text v General BIU V $ v % 9 Paste O v A v .00 00 0 E Merge & Center v Conditional Formatting a Q8 v fx A B D E F G H K M Day Code Bonus Decision Hired before Hired before Hired before States Weekday Comparison Data Last Day of Year Minimum Representative Salary Date Percent 12/31/21 1/1/10 1/1/15 2 lowa 1 Monday Tuesday 3% 3 Minnesota 62,000 2% Input Area Wisconsin Wednesday Thursday 4 1/1/20 1% 4 Friday Years Hire Date Day of Week Name Title Hire Date Year Hired Employed City 8/30/19 St. Paul Salary Region 125,350 Minnesota Code Hired Bonus Raise 9 Aalders Executive 2019 2.3 5 Friday 1,254 10 Babin Representative 65,000 Wisconsin 7.3 9/18/14 (Milwaukee 11/7/18 St. Paul 10/18/07 Des Moines 1/8/20 (Milwaukee $ 6/16/14 Milwaukee 9/3/15 St. Paul 11/6/15 (Milwaukee 12/14/09 St. Paul 4/3/20 (St. Paul 8/13/20 Des Moines 2014 4 Thursday Wednesday 1300 11 Bemhardsson Representative 54,000 Minnesota 105,750 lowa 55,125 Wisconsin 70,500 Wisconsin 65,400 Minnesota 64,750 Wisconsin 2018 3.2 3 540 12 Chavdarov Executive 2007 14.2 4 Thursday 3,173 Representative Representative Representative Representative 13 Diaz 2020 2.0 3 Wednesday 1410 1410 14 Duff is Monday Thursday 2014 7.5 1 15 Esteves 2015 6.3 4 654 16 Fabron 2015 6.2 Friday Monday 648 17 Griffith Representative 74,350 Minnesota 2009 12.0 1 2,231 Friday Thursday Monday Thursday Tuesday 18 Guan 19 Halloran Representative Representative 55,120 Minnesota 61,000 lowa 2020 1.7 5 is 2020 1.4 20 lordanou 21 Janowski 1/17/11 Des Moines Representative Representative 65,750 lowa 70,600 Wisconsin 115.750 Wisconsin 75,100 Minnesota 2$ 2011 11.0 1 1,315 6/5/14 (Milwaukee 2014 7.6 4 1,412 22 Kimura Executive 7/15/14 Milwaukee 24 2014 7.5 2 2,315 Representative 4/15/09 St. Paul 2009 12.7 3 Wednesday 2,253 23 Representative 3/3/14 (St. Paul 2/18/08 Milwaukee 11/8/16 Milwaukee 6/15/13 (St. Paul 10/11/16 St. Paul 7/14/20 Des Moines 4/17/18 St. Paul 1/31/14 Des Moines 4/17/12 (Milwaukee 73,125 Minnesota 75,180 Wisconsin 63,000 Wisconsin 118,000 Minnesota 24 2014 7.8 7.8 1 Monday is 1,463 Monday Tuesday 2008 13.9 1 2,255 Representative Executive Ekecuuve Representative 2016 5.1 2 630 2013 8.5 #N/A 2,360 53,800 Minnesota 2016 5.2 2 Tuesday 538 Representative 61,700 lowa 55,750 Minnesota 29 Reece 2020 1.5 2 Tuesday is Representative Representative 30 Saito 2018 3.7 2 Tuesday 558 31 Tennison 63,420 lowa 2014 7.9 5 Friday 1,268 72,900 Wisconsin 2 (4 1,458 32 Ungur 33 Vemon Representative 2012 9.7 Tuesday Monday Representative 3/9/20 St. Paul 53,750 Minnesota 2020 1.8 1 34 35 36 27 Kimura 23 lim Tuesday Representative Popresentative Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Calibri (Body) v 11 v A A ab Wrap Text v General BIU V $ v % 9 Paste O v A v .00 00 0 E Merge & Center v Conditional Formatting a Q8 v fx A B D E F G H K M Day Code Bonus Decision Hired before Hired before Hired before States Weekday Comparison Data Last Day of Year Minimum Representative Salary Date Percent 12/31/21 1/1/10 1/1/15 2 lowa 1 Monday Tuesday 3% 3 Minnesota 62,000 2% Input Area Wisconsin Wednesday Thursday 4 1/1/20 1% 4 Friday Years Hire Date Day of Week Name Title Hire Date Year Hired Employed City 8/30/19 St. Paul Salary Region 125,350 Minnesota Code Hired Bonus Raise 9 Aalders Executive 2019 2.3 5 Friday 1,254 10 Babin Representative 65,000 Wisconsin 7.3 9/18/14 (Milwaukee 11/7/18 St. Paul 10/18/07 Des Moines 1/8/20 (Milwaukee $ 6/16/14 Milwaukee 9/3/15 St. Paul 11/6/15 (Milwaukee 12/14/09 St. Paul 4/3/20 (St. Paul 8/13/20 Des Moines 2014 4 Thursday Wednesday 1300 11 Bemhardsson Representative 54,000 Minnesota 105,750 lowa 55,125 Wisconsin 70,500 Wisconsin 65,400 Minnesota 64,750 Wisconsin 2018 3.2 3 540 12 Chavdarov Executive 2007 14.2 4 Thursday 3,173 Representative Representative Representative Representative 13 Diaz 2020 2.0 3 Wednesday 1410 1410 14 Duff is Monday Thursday 2014 7.5 1 15 Esteves 2015 6.3 4 654 16 Fabron 2015 6.2 Friday Monday 648 17 Griffith Representative 74,350 Minnesota 2009 12.0 1 2,231 Friday Thursday Monday Thursday Tuesday 18 Guan 19 Halloran Representative Representative 55,120 Minnesota 61,000 lowa 2020 1.7 5 is 2020 1.4 20 lordanou 21 Janowski 1/17/11 Des Moines Representative Representative 65,750 lowa 70,600 Wisconsin 115.750 Wisconsin 75,100 Minnesota 2$ 2011 11.0 1 1,315 6/5/14 (Milwaukee 2014 7.6 4 1,412 22 Kimura Executive 7/15/14 Milwaukee 24 2014 7.5 2 2,315 Representative 4/15/09 St. Paul 2009 12.7 3 Wednesday 2,253 23 Representative 3/3/14 (St. Paul 2/18/08 Milwaukee 11/8/16 Milwaukee 6/15/13 (St. Paul 10/11/16 St. Paul 7/14/20 Des Moines 4/17/18 St. Paul 1/31/14 Des Moines 4/17/12 (Milwaukee 73,125 Minnesota 75,180 Wisconsin 63,000 Wisconsin 118,000 Minnesota 24 2014 7.8 7.8 1 Monday is 1,463 Monday Tuesday 2008 13.9 1 2,255 Representative Executive Ekecuuve Representative 2016 5.1 2 630 2013 8.5 #N/A 2,360 53,800 Minnesota 2016 5.2 2 Tuesday 538 Representative 61,700 lowa 55,750 Minnesota 29 Reece 2020 1.5 2 Tuesday is Representative Representative 30 Saito 2018 3.7 2 Tuesday 558 31 Tennison 63,420 lowa 2014 7.9 5 Friday 1,268 72,900 Wisconsin 2 (4 1,458 32 Ungur 33 Vemon Representative 2012 9.7 Tuesday Monday Representative 3/9/20 St. Paul 53,750 Minnesota 2020 1.8 1 34 35 36

Answer rating: 100% (QA)

Nested Formula A Nested formula is employed ... View the full answer