Formulas needed for M11, M12 and M13. Please use only Sum/Sumif/s, Count/countif/s, Vlookup, Min/max, IF/AND/OR/AVERAGE/MIN/MAX

AutoSave OFF 2021-Exl-MiniProj-RevEasier Home Insert Draw Page Layout Formulas Data Review View Tell me Share a Comments X55 H Y File View Sales Score 13 Tony E F M N R S U V w 1 (s] If someone's salary is #1, display 1 in L7; if #2, display 2; if #3, display 3. Use the Excel LARGE function: =LARGE(the_range_whose_data_is_to_be_ranked, #_in_ranking_such_as_1_2_3_4) 2 If someone's length on job (LOJ) is #1, display 1 in Le: if #2, display 2; if #3, display 3. Reference: Excel LARGE Function 3 ag, Inc. Skills involved: (1) Lookup, (2) IFested IF, (3) SUMIF, (4) COUNTIF, Tasks Part 1 : 4 les ||(5) references, (6) pivot table, (7) common functions such as MIN/MAX https://exceljet.net/excel-functions/excel-large-function Lookup an employee's Salary & length on job (LOJ) 5 mp Total points: 24 Learn by yourself (below): Enter Emp ID below: OR: ===== ==> Enter LName below: XI. exceljet_large.xlsx - Microsoft Excel 6 For tasks 1 & 2, students Home Insert Page Layout Formulas Data Review 7 need to learn the function 1) IF the emplyee's salary is among top-3, display ranking formula: 5 points G4 fx LARGE(D4:D11,1) 8 LARGE and functoin SMALL 2) If the emplyee's LOJ is among top-3, display ranking: = formula; 5 points 9 (Search online) (See col. N) A B E F G 10 Tasks Part 2: Group Statistics Data entry below: Formula design below: 1 LARGE(array, k) 11 First Name Sex Position Department Salary Hire Date DOB 3) Average Salary by Dept (3 points): 21 note: Engineering FALSE = formula in M11 2 12 Tom M Sales $30,678 4/3/99 24/07/76 4) Average Salary by Position (3 points): AVG = SUM / (enter a position) = formula in M12 3 First Last Score Rank M Product Me Marketing $30,257 11/9/95 03/07/64 5) Average Salary by Gender (3 points): COUNT (enter a gender) formula in M13 Sue 12 Brown 66 14 Stephanie F Engineer Engineering $72,230 8/27/96 30/10/63 5 Sarah Duncan 84 86 15 Caroline F Engineer Engineering $62,809 11/8/97 09/05/836) Department that has the highest slary for an individual: e formula: 3 points Justin Gatt 69 3 84 16 Lisa F Product Me Marketing $37,243 5/9/01 19/10/72 7) Position that has the highest slary for an individual: formula: 3 points Manfred Hollis 77 17 Rolanda F Admin Assi Marketing $33,364 2/26/01 16/03/76 1 free point for everyone. Troy Johnson 18 Roger M Receiving Warehouse $37,500 7/10/97 24/01/79 Tasks Part 3 : Group Statistics using Pivot Table Aubrey Sinclair 88 19 Matt M Product Me Marketing $56.744 5/10/01 29/11/73 Obtain the data in "3" and "7" above using pivot table. 2 + 2 points 10 Gen Tanaka 86 20 Tom M Admin Assi Accounting $29,341 3/21/94 24/07/72 21 Paul M Group Man Warehouse $53,700 9/21/95 13/11/76 Pivot table to present data for "3" begins from K23 Pivot table to present data for "7" begins from N23: 22 Bobby M Engineer Engineering $68,840 2/25/01 17/11/82 LiYou will list the highest salary for ALL positions from which you can easily see the highest among all positions) 23 Pat F Sales Sales $35,567 4/21/00 11/10/63 24 Sally F Technician Engineering $40,875 11/8/96 25/10/73 25 Pat M Accountant Accounting $48,968 12/17/94 20/04/74 26 Donald M Product Me Marketing $36,950 1/27/97 31/03/58 27 Girard M Engineer Engineering $54,561 8/26/00 27/05/79 28 Burt Admin Assi Admin $33,974 11/9/93 30/05/63 29 Tom M Design Spe Art $62,681 10/3/96 18/10/56 30 James M Inventory Warehouse $40,600 1/20/00 24/08/69 31 David M Chief Scien Rand D $97.757 4/4/95 09/11/69 32 Elenor M Group Adrr R and D $38,444 6/14/01 19/08/70 33 Renee F Accountant Accounting $41,577 5/11/95 29/03/65 34 Laurie F Sales Sales $41,414 12/10/93 01/11/61 35 Charlene F Engineer Engineering $65,282 7/1/97 09/02/78 36 May F Admin Assi Accounting 04/12/62 37 Theo M Admin Assi Marketing $30,343 8/29/96 25/04/79 38 Ann F Inventory Warehouse $39,800 12/14/00 08/12/66 39 James M Group Man Marketing $93,057 5/23/95 11/09/63 40 Ree F Admin Assi Admin $39,281 10/4/96 05/06/60 41 Ree F Admin Assi Admin $38,840 4/3/96 20/10/71 42 Ken M Group Ader Accounting $40,669 7/16/96 17/06/81 43 Mark M Inventory Warehouse $38,600 2/26/97 08/03/67 44 Matthew M Shipping Warehouse $39,550 6/15/96 13/09/67 45 Alyssa F Engineer Engineering $44,918 7/12/97 22/09/57 46 Malcolm M Product Me Marketing $34,223 3/28/92 26/01/57 47 Claudia Engineer Engineering $38,556 8/27/97 13/03/72 48 Janice Admin Assi Admin $37,615 4/21/96 14/03/78 49 Bill M Chief Scien Rand D $83,776 10/13/96 17/02/74 50 Melissa Design Spe Art $63,018 11/9/92 31/08/71 51 Alex M Unit Manag Admin $41,762 11/8/99 09/02/75 52 Megan F Group Ader Marketing 12/15/98 09/01/77 53 Chris M Admin Assi Admin $41,092 11/25/97 10/05/81 54 Dick M Technician Engineering $33,574 7/21/00 28/09/70 55 Joey F Admin Assi Admin $37,796 2/27/95 03/05/78 56 Martin M Group Ador Marketing $39, 142 1/20/00 10/07/64 57 Mike M Admin Acci Marketing 427 511 7/22/9A 19/09/5 ReadMe Tasks + Ready HH 110% $32.120 1/20/97 $38.923 s