Question

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.

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

Answer 1

Nested Formula: A Nested formula is used to formulate two or more formulas in a single formula. We can use the formulas like IF, AND in a single formula.

In Cell L9, write the below formula

=IF(AND(B9="Representative",E9<$G$3),$G$3-E9,"0")

Copy the same formula from L9 and paste it from L10 to L33