excell formulas??

Question

how would you create a formula to multiply the hours worked and overtime hours which is time a half of there pay rate, times pay rate and add in commission. holiday pays and then subtract any deductions and then give a total?? anyone have anyclue cuz i dont

alls im looking for is a formula to take the hours worked mutlipy that by the rate of pay and then take the overtime multiply that and then add ANY if there is any holiday pay or commissions and if there are any deductions subtract it.. ..say you just punch in 40.00 hrs with 3.75 overtime hours rate of pay is 15.00 and 73.75 in commission how do i creat a formula to multiply the hours and overtime and then add the commissions to get a total in the next column.....

oh and yeah to mutliply the OT by time a half....

where do i put the formula in that last column and rows??

Answer 1

You have to create a separate column for each item: hours worked, overtime hours, pay rate, commission, holiday hours; deductions, total.

Try writing out the formula with words, and then replacing each word with the cell (like A4 or B12) that contains that value.
So:
(hours worked)(pay rate) + (overtime hours), ((1.5)(pay rate)) + (holiday hours)(pay rate) + commission - deduction = total

Answer 2

I wouldn't use a single formula for this...It would be a series of formulas, with a grand total of each of the formulas in a final cell.

I'd set it up with one row each for the following items:
* Hours worked (up to 40/week) - multiply this by the hourly rate to get your subtotal. The hours worked would be a field that is changed with each pay period, but the hourly rate would be a constant for each person. If A1 is hours worked, and A2 is the hourly rate, the formula would read =A1*A2
* Overtime hours worked - multiply this by the hourly rate, and then multiply that by 1.5. Again, the hourly rate would be a constant, but the overtime hours would be changed manually with each pay period. If B1 is the overtime hours, and B2 is the hourly rate, the formula would read =(B1*B2)*1.5
* Holiday pay would be done in the same way as the regular pay, but would also be on a separate line, so it's easy to identify.
* Commissions are usually calculated in a separate report. I would just leave this as a blank field that is edited when commissions are due.
* Deductions are tricky. For payroll purposes, you probably have to list these all individually, so that they can be separated out again at tax time. I would either leave these as individually edited fields (like commission), or have a subtotal for earned income, and then have the percentage for each multiplied in. Assuming that A3 is the regular earnings, B3 is the overtime earnings and C3 is the commissions, and the deduction is 10%, the formula would read: =(A3+B3+C3)*.10

Once you have all of these ready, just total all of them up. If the deductions comes from row D and all of the subtotals are in column 3, the formula would read: =A3+B3+C3-D3

If I have confused you, or if you need more help, feel free to email me.

Answer 3

No one can tell you the answer without more information. Exactly what is driving commissions, what holiday's you're talking about and what deductions you need to subtract. If everything is driven off hours worked, use that as the base, and create if, then formula's off of hours.