new to excel need help with function
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 |
new to excel need help with function
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(A240,(A2-40)*B2*1.5,0) or: =MAX(0,A2-40)*B2*1.5 to avoid using IFs. Hope this helps. Pete On Jul 25, 1:16*pm, new to excel <new to wrote: *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 |
new to excel need help with function
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(C340,40,C3)) E3 =IF(A3="","",(D3*B3)) F3 =IF(A3="","",IF(C340,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. |
new to excel need help with function
G'day Pete
You just blew my example out of the ball park...LOL...:-) Certain your way will be much simpler Cheers Mark. |
new to excel need help with function
Thanks for your feedback, Mark - I find that simpler is better.
I'd like a job at that company, as well !! <bg Pete On Jul 25, 2:28*pm, "NoodNutt" wrote: G'day Pete You just blew my example out of the ball park...LOL...:-) Certain your way will be much simpler Cheers Mark. |
All times are GMT +1. The time now is 12:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com