 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2008Function IF statement
I need to know how to get this column to say: If the hours worked are more than 40 then take time and a half and multiply that times the difference between the hours worked and 40 and in that same cell add the regular hour to the overtime pay. i've got it about 98% done but cannot get the last part. please help me with this if you can. my spreadsheet is below and the IF statement looks like this: Hours Worked Hourly Pay Commission Earned Hourly Pay Earned C D E F 1 30.00 $10.00 $562.50 $300.00 2 25.00 $10.00 $300.00 $250.00 3 40.00 $10.00 $825.00 $400.00 4 45.00 $15.00 $- $112.50 5 40.00 $12.50 $- $500.00 6 35.00 $10.00 $267.00 $350.00 In cell F: =IF(C2>40,((D2/2)+D2)*(C2-40),C2*D2) Thanks so much in advance! Raymond =IF(C1<=40,C1*D1,C1*D1+(C1-40)*D1*1.5). What does Commissioned Earned have to do with it? Tyro > I need to know how to get this column to say: If the hours worked are more > than 40 then take time and a half and multiply that times the difference [quoted text clipped - 19 lines] > Thanks so much in advance! > Raymond correction: ==IF(C1<=40,C1*D1,40*D1+(C1-40)*D1*1.5) > =IF(C1<=40,C1*D1,C1*D1+(C1-40)*D1*1.5). What does Commissioned Earned have > to do with it? [quoted text clipped - 24 lines] >> Thanks so much in advance! >> Raymond > I need to know how to get this column to say: If the hours worked are more >than 40 then take time and a half and multiply that times the difference [quoted text clipped - 16 lines] >Thanks so much in advance! >Raymond It is not clear to me exactly what you want. To compute the total hourly + overtime compensation, you could use this formula: =D2*(C2+MAX(0,C2-40)*1.5) --ron > On Fri, 1 Feb 2008 18:36:00 -0800, Raymond > <Raymond@discussions.microsoft.com> [quoted text clipped - 33 lines] > > --ron That returns an incorrect result when hours >40. The first reference to C2 needs to be reduced to straight-time hours if hours >40: =D2*(MIN(C2,40)+MAX(0,C2-40)*1.5)  SignatureBiff Microsoft Excel MVP >That returns an incorrect result when hours >40. > >The first reference to C2 needs to be reduced to straight-time hours if >hours >40: > >=D2*(MIN(C2,40)+MAX(0,C2-40)*1.5) Yup, I was looking at a wrong total along the way. =C2*D2+D2*0.5*MAX(0,C2-40) is shorter with no MIN --ron >> I need to know how to get this column to say: If the hours worked are more >>than 40 then take time and a half and multiply that times the difference [quoted text clipped - 25 lines] > >--ron Should be: =C2*D2+D2*0.5*MAX(0,C2-40) --ron >Should be: > >=C2*D2+D2*0.5*MAX(0,C2-40) Or a bit shorter: =D2*(C2+0.5*MAX(0,C2-40)) --ron Try this: =IF(C2="",0,MIN(C2,40)*D2+MAX(C2-40,0)*(D2*1.5)) SignatureBiff Microsoft Excel MVP > I need to know how to get this column to say: If the hours worked are more > than 40 then take time and a half and multiply that times the difference [quoted text clipped - 19 lines] > Thanks so much in advance! > Raymond Why make it so complex? with Min Max?? Just take the hours <= 40 at straight time and then over 40 at time & a half. Tyro > Try this: > [quoted text clipped - 23 lines] >> Thanks so much in advance! >> Raymond >Why make it so complex? I guess complexity is a matter of opinion. At least my formula returns the *correct* result! <g> Your formula returns the incorrect result when hours >40. Hours = 48 Rate = 10 OT rate = 15 Reg hours = 40*10 = 400 OT hours = 8*15 = 120 Total pay = 520 Your formula = 600 Are you hiring? I'd love to work for you! <BG> SignatureBiff Microsoft Excel MVP > Why make it so complex? with Min Max?? Just take the hours <= 40 at > straight time and then over 40 at time & a half. [quoted text clipped - 28 lines] >>> Thanks so much in advance! >>> Raymond Mistake. I corrected it. To obfuscate serves no purpose. I believe in the KISS principle. Tyro > >Why make it so complex? > [quoted text clipped - 50 lines] >>>> Thanks so much in advance! >>>> Raymond |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.