Microsoft Excel 02 Payroll Part 2 If formulas and functions

before you get started with a lesson let me give you a quick overview of how to use this series of videos this is a series that covers Microsoft Office 2013 using documents spreadsheets and presentations I'm a teacher I work in Tolleson Arizona at a high school called Western high school these assignments are selected to be exactly like the choose view in the real world so using office is what you'll use in a real office for the real business there is an assignment book that accompanies every video each page in the assignment book has a checklist of the things that you should accomplish in each lesson watch the video lesson to see how things are done after you create your own document using the video as a model you may have some modifications such as your own business names or your own paragraph so your own data but it'll look very similar now you can either watch the whole video through at one time or pause it as you go through it finally print the document and your classroom teacher will great so now let's move on to your next assignment welcome to assignment number two for Microsoft Excel we're going to extend assignment number one using the payroll spreadsheet that we had created earlier what I'd like to do in this assignment is add a new formula using the if formula and also adding the idea of paying overtime to our employees so you notice here in column D that we have the number of hours that each employee worked the first employee worked 41 hours we're going to give him a bonus for his extra hour of overtime so I'm going to start by inserting a column here in column E if I click the column header the entire column is selected I can right click on the column header and choose the insert button and it will now give me a new column to work with the first column column D was the hours worked now I'm going to say this is the overtime hours the overtime hours is a calculated field so let's come up with a formula that will tell us how many hours extra that this employee worked at first it would seem simple we would just say equals the number of hours the person worked subtract 40 and that will tell us how many hours of overtime this person has it works great if you have 41 but there's some errors if you have less than 40 I'm going to fill the column down and you will see that when we come to Paul Smith in row number 8 he actually gets a negative one hours it's a negative hours that he's worked that doesn't seem to work too well what we would rather give is Paul should have zero hours of overtime not a negative number so let's revise the formula a little bit so let's go up to here and this time I'm going to type in an equal sign with the word if if is a command that says let's take a logical test and we will a logical test is either an equal sign greater than or less than and then we'll give it a value depending on if that test is true or not and if it's false we'll give it a different number watch how this works the question is if his hours worked is greater than 40 and a comma the value if it is greater than 40 should be his hours worked minus 40 a comma and if he worked less than 40 hours then let's just give him zero for this column which would be zero hours of overtime a closed parenthesis and press press return so he still gets one hour of overtime but now when I fill the formula down you see that the people that worked less than 40 hours gets zero in their form in their formula instead of negative numbers and so this is the correct formula for calculating overtime pay now how about the pay what does that do let's go back and review this here if I double click here you will see that this formula takes the number of hours to the hourly wage and fills it in column F that's great but now we give them a bonus for working overtime so let's create a new column and call it overtime bonus now in this formula we're going to calculate a bonus for the number of overtime hours they worked so I'm going to say equals 0.5 and use the time sign 0.5 times their hourly wage so we're going to give them half of their hourly wage times the number of overtime hours they worked what that does is it gives us a formula to give them time and a half or one and a half times for each hour that they worked overtime press Enter so since John Kern earned one hour of overtime we give him his full 41 hours of pay plus an extra 50 percent of his pay for one of those hours let's fill this down and see what we have for the rest of the people some of the people have zero this one got no extra time bonus because he worked 39 hours some of these people worked 40 hours and still got zero time why because the formula is about how many hours beyond 40 that we're calculating so they got paid their full wage for the first 40 hours now how about the total pay let's put in a new column the total pay is simply equals to their regular 40 hours of pay plus their overtime bonus and a return and let's use the fill down option some of the numbers are too big so we will expand the column and this is their total pay down here at the bottom we calculated maximums minimums averages and totals for all of these formulas let's move these to the right calculate the totals now and see we can see what everybody earned including their overtime this is assignment number two save it and print it again

Channel: Programming w/ Professor Sluiter

Views: 262,120

Keywords: Office, 2013, High, School, Computer, Classroom, Microsoft Office (Software), Microsoft Excel (Software), Excel, Payroll, Business, microsoft excel tutorial, microsoft excel, payroll in excel, excel tutorial, excel payroll tutorial, payroll excel, microsoft excel (software), how to make payroll in excel, payroll system in excel, how to use excel, payroll excel tutorial, how to do payroll, excel payroll, if formula in excel, microsoft excel 01 payroll part 2

Id: bOLr6r-n4Pk

Length: 6min 16sec (376 seconds)

Published: Fri Jul 11 2014