new to excel need help with function

[new to excel]

Total Hours Worked Wage/Hour Regular Pay Overtime Gross Pay

33 $38.00 $1,254.00
40 $29.00 $1,160.00 $-
46 $9.50 $437.00
46.5 $28.75 $1,336.88
40 $7.50 $300.00 $-

Can someone show me the IF that calculates the regular pay and the over
time pay at 1.5 over 40 hours

Thanks

 

[Pete_UK]

Assiming your first hours worked (33) is in A2 - put this in C2:

=MIN(A2,40)*B2

This chooses the smaller of the hours worked and 40, so it does away
with the need for an IF. Put this in D2 to evaluate the overtime:

=IF(A2>40,(A2-40)*B2*1.5,0)

or:

=MAX(0,A2-40)*B2*1.5

to avoid using IFs.

Hope this helps.

Pete

 

[NoodNutt]

G'day New to Excel

Here is the normal view you would see when all the formula's are in place:

A B C
D E F G
H

Row 1 Employee Employee Total
T1.0 T1.0 T1.5 T1.5
Gross
Row 2 Name Rate Hours
Hours $ Hours
$ $
Row 3 Jack 38.00 60.00
40.00 1,520.00 20
1,140.00 2,660.00
Row 4 Jill 29.00 60.00
40.00 1,160.00 20
870.00 2,030.00

And so on & on................

By the way, where does the Queue start to get a job at this company with
these hourly rates.....LOL.....

Now for the formula's in each Cell: Starting @ D3

D3 =IF(A3="","",IF(C3>40,40,C3))
E3 =IF(A3="","",(D3*B3))
F3 =IF(A3="","",IF(C3>40,C3-D3))
G3 =IF(A3="","",(F3*B3)*1.5)
H3 =IF(A3="","",(E3+G3))

Copy and paste down as required

Now, this is where we get to the slighty harder stuff, particularly for
newbies, by that I mean, Name Ranges, Data Validation dropdown lists and
lookup tables to automate the sheet for you.

If you don't think you ready for all that, start entering the info in
manually until you get more familiar & comfortable using Excel and some of
it's functions.

Let me know if you're feeling up to the task and I will step you through the
other stuff.

Good luck
HTH
Mark.

 

[NoodNutt]

G'day Pete

You just blew my example out of the ball park...LOL...

Certain your way will be much simpler

Cheers
Mark.

 

[Pete_UK]

Thanks for your feedback, Mark - I find that simpler is better.

I'd like a job at that company, as well !! <bg>

Pete