Question

W/E	Emp#	EmpName	# hours	Pay/Hour	Gross Pay	M/S	#dep	Tax Pay	Tax	Net
4/11/18	11	John	20
4/11/18	14	Mary	50
4/11/18	19	Nancy	30
4/11/18	22	Sam	10
4/11/18	25	Carol	60
4/18/18	11	John	60
4/18/18	14	Mary	40
4/18/18	19	Nancy	25
4/18/18	22	Sam	45
4/18/18	25	Carol	42
4/18/18	18	Wally	32
	Emp#	Pay/Hour	M/S	#dep
	11	20	M	1		Submit the excel sheet to me. No excel formula - no credit.
	12	30	S	3
	18	20	S	5
	19	30	M	2
	22	40	M	4
	23	30	S	6
	25	20	M	2
	step1: write a vlookup to fill in column D - if an employee is not in the table, do not get N/A (hint: iferror)
	step2: compute gross pay column E
	if an employee works under 40 hours, it is # of hours time hourly rate
	if an emplyee works more than 40 hours, the number of hours over 40 are paid 1.5 times the hourly rate
	example: X works 60 hours. X get 16/hour. X get 40 times 16 plus 20 times 24.
	step3: write a vlookup to fill in column F - if an employee is not in the table, do not get N/A (hint: iferror)
	step4: write a vlookup to fill in column G - if an employee is not in the table, do not get N/A (hint: iferror)
	step5:	compute column H Tax Pay
	multiply the # of dependents (column G) by 10 and subtract that from gross pay
	if column F is single - subtract 20 more
	if column F is married - subtract 50 more
	example: column E is 1200, 3 dependents, married -- tax pay is 1200 minus 3 times 10 minus 50
	example: column E is 700, 2 dependents, single -- tax pay is 700 minus 2 times 10 minus 20
	step6: compute the tax
	if tax pay < 500, no tax
	if tax pay < 1000, 5% of tax pay over 500
	else tax is 25 plus 10% of the tax pay over 1000
	example: tax pay 350   no tax
	example: tax pay 650   no tax on first 500 -- 5% on (650 minus 500) -- 5% of 150 -- 7.50
	example: tax pay 1200 tax is 250 plus 10% of (122 minus 1000)
	step7: net pay is tax gross pay minus tax

Answer 1

To show the formula used for each step i will first show the formula used in respective column and then show the final answer to the problem.

Step 1:

Step2:

Step 3:

Step 4:

Step 5:

Step6:

Step 7:

Final Answer:

The formulas shown in the above steps are broken into different images to make it readable and clear. These formulas are entered in the respective cells as shown in the final answer. While entering this data into excel keep in mind that your data is entered in exactly the same cells as shown in the image above to get the right answer.

Also, in place of "No Info" you can enter any text as you desire. Just change the same in the formula to get the required text.