Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function 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(C240,((D2/2)+D2)*(C2-40),C2*D2) Thanks so much in advance! Raymond |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF statement
=IF(C1<=40,C1*D1,C1*D1+(C1-40)*D1*1.5). What does Commissioned Earned have
to do with it? Tyro "Raymond" wrote in message ... 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(C240,((D2/2)+D2)*(C2-40),C2*D2) Thanks so much in advance! Raymond |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF statement
On Fri, 1 Feb 2008 18:36:00 -0800, Raymond
wrote: 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(C240,((D2/2)+D2)*(C2-40),C2*D2) 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF statement
Try this:
=IF(C2="",0,MIN(C2,40)*D2+MAX(C2-40,0)*(D2*1.5)) -- Biff Microsoft Excel MVP "Raymond" wrote in message ... 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(C240,((D2/2)+D2)*(C2-40),C2*D2) Thanks so much in advance! Raymond |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF statement
"Ron Rosenfeld" wrote in message
... On Fri, 1 Feb 2008 18:36:00 -0800, Raymond wrote: 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(C240,((D2/2)+D2)*(C2-40),C2*D2) 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 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) -- Biff Microsoft Excel MVP |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF statement
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 "T. Valko" wrote in message ... Try this: =IF(C2="",0,MIN(C2,40)*D2+MAX(C2-40,0)*(D2*1.5)) -- Biff Microsoft Excel MVP "Raymond" wrote in message ... 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(C240,((D2/2)+D2)*(C2-40),C2*D2) Thanks so much in advance! Raymond |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF statement
correction: ==IF(C1<=40,C1*D1,40*D1+(C1-40)*D1*1.5)
Tyro "Tyro" wrote in message t... =IF(C1<=40,C1*D1,C1*D1+(C1-40)*D1*1.5). What does Commissioned Earned have to do with it? Tyro "Raymond" wrote in message ... 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(C240,((D2/2)+D2)*(C2-40),C2*D2) Thanks so much in advance! Raymond |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF statement
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 -- Biff Microsoft Excel MVP "Tyro" wrote in message ... 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 "T. Valko" wrote in message ... Try this: =IF(C2="",0,MIN(C2,40)*D2+MAX(C2-40,0)*(D2*1.5)) -- Biff Microsoft Excel MVP "Raymond" wrote in message ... 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(C240,((D2/2)+D2)*(C2-40),C2*D2) Thanks so much in advance! Raymond |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF statement
Mistake. I corrected it. To obfuscate serves no purpose. I believe in the
KISS principle. Tyro "T. Valko" wrote in message ... 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 -- Biff Microsoft Excel MVP "Tyro" wrote in message ... 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 "T. Valko" wrote in message ... Try this: =IF(C2="",0,MIN(C2,40)*D2+MAX(C2-40,0)*(D2*1.5)) -- Biff Microsoft Excel MVP "Raymond" wrote in message ... 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(C240,((D2/2)+D2)*(C2-40),C2*D2) Thanks so much in advance! Raymond |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF statement
On Fri, 01 Feb 2008 22:01:54 -0500, Ron Rosenfeld
wrote: On Fri, 1 Feb 2008 18:36:00 -0800, Raymond wrote: 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(C240,((D2/2)+D2)*(C2-40),C2*D2) 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 Should be: =C2*D2+D2*0.5*MAX(0,C2-40) --ron |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF statement
On Fri, 1 Feb 2008 22:42:28 -0500, "T. Valko" wrote:
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) -- Biff Microsoft Excel MVP 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function IF statement
On Fri, 01 Feb 2008 23:34:50 -0500, Ron Rosenfeld
wrote: Should be: =C2*D2+D2*0.5*MAX(0,C2-40) Or a bit shorter: =D2*(C2+0.5*MAX(0,C2-40)) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function Conflicts with an If/Then Statement | Excel Worksheet Functions | |||
WORKDAY Function in an IF statement | Excel Worksheet Functions | |||
Round function in If statement | Excel Discussion (Misc queries) | |||
Using date function in an if statement | Excel Worksheet Functions | |||
Countif Function with and Statement | Excel Worksheet Functions |