Function IF statement

[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
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

 

[Tyro]

=IF(C1<=40,C1*D1,C1*D1+(C1-40)*D1*1.5). What does Commissioned Earned have
to do with it?

Tyro

 

[Ron Rosenfeld]

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

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

 

[T. Valko]

Try this:

=IF(C2="",0,MIN(C2,40)*D2+MAX(C2-40,0)*(D2*1.5))

 

[T. Valko]

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)

 

[Tyro]

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

 

[Tyro]

correction: ==IF(C1<=40,C1*D1,40*D1+(C1-40)*D1*1.5)

Tyro

 

[T. Valko]

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>

 

[Tyro]

Mistake. I corrected it. To obfuscate serves no purpose. I believe in the
KISS principle.

Tyro

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

Should be:

=C2*D2+D2*0.5*MAX(0,C2-40)

Or a bit shorter:


=D2*(C2+0.5*MAX(0,C2-40))


--ron