Question

The medical group offers its employees an additional retirement benefit based on the employee’s position and years of service. Employees who are with the practice for three or more years will receive an additional contribution to their retirement account. Calculate this benefit by entering an IF function in cell M3 on the Payroll Details worksheet. If an employee is a physician and has been working in the medical group for three or more years, the function should multiply the Gross Pay This Week by 8%. For all other employees who have been working at the medical group for three or more years, the function should multiply the Gross Pay This Week by 5%. Copy and paste the function into the range M4:M22.

Enter a formula into cell O3 on the Payroll Details worksheet that calculates the Net Payment for each employee. Your formula should subtract the values in the range I3:K3 from the Gross Pay This Week in cell F3. The range I3:K3 includes the following: Total FICA (cell I3), Federal Tax (cell J3), and 401K Retirement (cell K3). Copy and paste this formula into the range O4:O22.

#please show the formula not the number pls

o Net Payment PA Employee Last Name, First Name Rollet, Heather Lake, Connie Antoine, Gerry Kade, Michael Long, Shari James, Don Patel, Saniey Washington, Janet Shin, Burt Kumar, Denise Xu, Weimin Dixey. Bernard Blane. John Li, Wei Blanch. Maurice Wan, Stanley Harlen, Lori Hothe, Peggy Michaels, Angela Daniels, Max Position Practice Orthopedics PA Obstetrics PA Adult Internal Med PA Pediatrics Physician Adult Internal Med Physician Orthopedics Physician Adult Internal Med Physician Pediatrics Physician Obstetrics Physician Obstetrics Physician Orthopedics Physician Orthopedics Physician Pediatrics Group Staff Adult Internal Med Staff Pediatrics Staff Group Staff Obstetrics Staff Group Staff Orthopedics DIE н тук L M N Little Hills Medical Group Payroll Detail 401 K Total Years of Pay Year to Gross Pay Social Medicare Total Federal 401K Company Retirement Retirement | Service Date This Week Security Tax Tax FICA Tax Retirment Match Benefit Benefits 0.1 $ 20,400 $ 600 $ 18.00 021 S 22,950 $ 675 $ 27.00 0.51 $ 24,820 $ 730 $ 36.50 0.8 $ 29.410 $ 865 $ 51.90 1 $ 78,200 $ 2.300 $ 207.00 $ 71,910 $ 2,115 $ 190.35 91,800 $ 2,700 $ 216.00 62,050 $ 1,825 $ 127.75 104,550 $ 3,075 $ 317.00 7 $ 115,600 $ 3.400 $ 317.00 8 $ 118,150 $ 3,475 $ 317.00 10 $ 137,190 $ 4,035 $ 317.00 16 $ 147,050 $ 4,325 $ 317.00 7 $ 19,550 $ 575 $ 23.00 1 $ 19,618 $ 577 $ 34.62 1 $ 29,410 $ $ 43.25 2 $ 22,950 $ 675 | $ 33.75 4 $ 26,180 $ 770 $ 30.80 S$ 36,040 $ 1,060 $ 31.80 9 $ 39,270 $ 1,155 $ 69.30 Falcool alloroololonthaalal Staff 865

Answer 1

From the description I understand that the Retirement Benefit will be 0 for any employee whose "Years of Service" is < 3. The formula is derived based on this assumption.

Formula in M3: =IF(AND(B3 = "Physician", D3 >= 3), F3*8%, IF(D3 >= 3, F3*5%, 0))

In case "Position" is not entered in proper case (for example, physician or PHYSICIAN or PHysician), please use the below formula in M3

=IF(AND(UPPER(B3) = "PHYSICIAN", D3 >= 3), F3*8%, IF(D3 >= 3, F3*5%, 0))

Formula in O3: =F3-SUM(I3:K3)