Needing help with a payroll formula fast

[anthony.fowble]

Attached is a prntscr of my workbook,
Im wanting to start by writing a formula for Patti Stonesifer (the 1st employee in the worksheet)’s gross pay in the Gross Pay column. Regular pay is the employee’s pay for up to 40 hours and is computed as hourly rate * hours worked. Overtime pay is paid for overtime hours. Overtime pay is computed as hourly rate * overtime rate * overtime hours.

• reference Overtime rate in the gross pay formula using absolute cell reference form, i.e. $C$3 so that the formula can be copied. The gross pay is Regular Pay + Overtime Pay. When her gross pay formula is correct, copy it down through the remaining employee’s gross pay cells

but i am having the hardest time coming up with a formula,

can you help? PLEASE!!
hope this makes sense
Anthony


Screenshot ------> http://i1058.photobucket.com/albums/t419/anthonyfowble1/12.jpg[/QUOTE]

 

[Jonmo1]

Welcome to the board..

here's a basic syntax


OverTimeThreshold = 40

Regular Pay
=MIN(OverTimeThreshold,HoursWorked)*RegularPay

Overtime Pay
=MAX(0,HoursWorked-OverTimeThreshold)*OvertimePay

Gross Pay
=RegularPay+OvertimePay


Hope that helps.

 

[anthony.fowble]

Thanks for the quick reply

how would i make it where the cells are referenced all in the same way as a single formula

 

[Jonmo1]

Try

=($E6*$D6)+($F6*($D6*$C$3))


Note, probably not a good idea to have real people's names in that picture...

 

[anthony.fowble]

All the last names are phonies(made sure of it before i posted)
ill give the formula a try real quick,
thank you so much!!!

 

[anthony.fowble]

Worked Wonders,
Many many kudos to you

 

[anthony.fowble]

hey i need help with one more thing real quick

i need to perform this

1. formula using nested IF function that classifies the employees as follows (hours worked = regular hours + overtime hours):
   1. hours worked > 45: “Overworked”
   2. 45>=hours worked > 35: “OK”
   3. hours worked <= 35: “Underworked”

 

[Jonmo1]

Try

=LOOKUP(HoursWorked,{0,36,46},{"Underworked","OK","Overworked"})

 

[anthony.fowble]

it gives me a #n/a error message

 

[Jonmo1]

Sounds like the number you have that represents the hoursworked is not really a number, but a "number stored as text"

It works for me..

Excel Workbook
	A	B
1	12	Underworked
2	30	Underworked
3	35	Underworked
4	36	OK
5	45	OK
6	46	Overworked
7	50	Overworked
Sheet1