in the last video we saw that number formats can be applied to cells whether
or not they contain data and that the data takes the format of the cell it's
entered into in this video we'll see that cells can contain calculation
formula and that any data entered into those cells will follow the calculation
rules you've applied it's a very powerful feature of Excel let's suppose
that you have a workforce of six people working Monday through to friday each is
paid eight dollars an hour but if they work more than 20 hours a week they go
to $25 bonus we're going to create a worksheet to record their hours but it
will also calculate whether they've earned that bonus and what their final
weeks pay will be let's start by laying out the headings for our worksheet we
need the following columns along the top named hours for each day of the working
week cumulative hours worked for the week for each person their hourly rate
their cumulative basic pay whether they've earned a bonus the amount of the
bonus and then the total pay for the week next we need a row for each person
on our workforce it's also good practice to give each row an independent
reference number we'll keep this simple and go from one to six so here's the
layout next will format the cells it's always helpful to add some dummy data as
you format the cells that way you know if you're getting the formatting and the
calculations right the first column with the names of our workforce needs to be
formatted as text
the next five columns are the hours
worked and that needs to be a number
the next column the cumulative total of hours per person needs to be the sum of
the previous five cells and also needs to be formatted as a number in the next
column will enter the hourly rate and we'll see how this worksheet layout can
be simplified in the following video but let's apply an accounting format here
now we've got enough data cells to calculate the basic pay so we'll enter
that calculation into the next column by multiplying the hourly rate by the hours
worked and we'll use an accounting format
we've also got enough data to decide if each individual has worked enough hours
to qualify for a bonus in order to calculate this will enter a formula into
the next column let's use a simple logic formula if the hours worked is equal to
or greater than 20 then the result is true and Excel will print yes if it's
less than 20 the result is false and Excel will simply print a blank space
the next column will contain the amount of the bonus again we'll use an
accounting format and we can simplify this in the next video in the final
column we enter another formula that will calculate the total pay basic pay
with or without bonus depending on whether they've earned it again we'll
use the same logic formula if hours worked is equal to or greater than 20
then add the bonus some to their basic pay and print the result if not just
print the basic pay
note that we've done all of this without entering any real data into our
worksheet let's test it by adding some to each worker you'll see that the
moment we get 20 hours xcel tells us that John has earned the bonus and adds
it to his total pay automatically
Mary's house did the fiber a bonus so her total pay is
just her basic pay all of this is done automatically for us by Excel because
we've specified both the format and the calculations we need for each cell
before we've even entered any data and of course using the formula is a very
powerful aspect for Excel and there's a lot more you can do with for matching
and calculation formula you could specify clock on and clock off times for
each day and let Excel calculate the number of hours and minutes worked
adding this to the worksheet you could transfer the weekly earnings to a second
worksheet and calculate the tax deductions for each individual and so on
in the next video we look at how to make the worksheet easier to read and more
efficient and will change the currency symbol from pound sterling to US dollars
just to make things more comfortable for you eTop Technology we're here to help
if you like these videos please subscribe to our YouTube channel if you
found this video helpful check out some of our most recent videos and if you're
looking for a new IT partner please visit our website
you