NOTES FOR EXCEL –
GROSS PAY EQUATION
If you recall from
class, you can calculate the gross income by doing what we do with every other
problem on paper, but Excel will do the work for you using a designated formula.
Row 1 = Headers for
each column, so all your numerical information starts on Row 2.
spreadsheet that I gave you today, the gross pay formula would be as follows:
1.5*B2*G2 + 2*B2*H2
*Please note that
the associated letters (B, F, G, & H) will change if you have a
different number of columns. (look below to see what each letter represents).
***What this does is
the following (in order with the equation):
B2 = regular pay
F2 = regular hours
G2 = regular
H2 = special or
The formula takes
your regular pay *
regular hrs + 1.5 * regular pay * regular OT hrs + 2 * regular pay * DBL OT
YOU MUST FIRST
PLACE AN EQUAL SIGN PRIOR TO THE EQUATION
AFTER COMPLETING THE
EQUATION, HIT “ENTER.” Excel will then compute the total gross income for that
After this is
complete, go the cell that you just placed your answer in. - My sheet it’s
Hit CTRL C for
Now highlight all
the cells below respective to the # of additional problems that you will have
(hold down shift to highlight more than one cell at a time)
Once they are
all highlighted, hit CTRL V to paste the equation in all of those cells.
ADJUST EACH EQUATION WITH ITS RESPECTIVE ROW NUMBER (i.e. – row 3’s
equation will match row 2’s, but all the “2”s will be “3”s and so on.
Highlight to check if you want.
formulas are pasted in, it will give you all the gross pay totals for the
already typed the necessary information in there, the totals are
complete. Otherwise, the totals should all read “$0.00” until you type
the proper information into your spreadsheet. Excel will adjust the gross
pay totals as you type the information in.
In addition, please
remember that pay rates and gross income totals must have a dollar sign with
them. To accomplish this task, highlight all of the cells that require this
(remember to hold shift or ctrl to highlight multiple cells at once), then hit
FORMAT, select CELLS off of the dropdown menu, and then choose CURRENCY. The
“$” sign will now be placed with each of these values.
To select the
number of decimal places in a cell, go through the same process, but select
NUMBER (for values that don’t need a “$” sign) and look to the right to choose
“2” in the DECIMAL section.