two IF's in one formula?

[Omia]

My spreadsheet has total hours worked by employees, and their pay rate. I have to calculate their regular pay plus any overtime pay. Anything over 40 is considered overtime.

b4=hours worked
b3=pay rate

So I've tried this formula:
=IF(B4>40,B3*40), IF(B4<40,B3*B4)

but I keep getting the #value! error.

So i tried this as well:
=IF(B4>40[B4*40],[B3*B4])

for the logical test, if true, if false formula. But that always comes up with an error message.

Any help is greatly appreciated!

[NBVC]

Try:

=B3*IF(B4>40,40,B4)

But I am not sure that is really the formula you want?

Do you want to only multiply anything above 40 hrs by a certain value, and anything under 40 by the regular rate?

If so, you need to supply more info about those rates and their respective locations...

[zbor]

You don't need 2 IF's

=IF(B4>40,B3*40,B3*B4)

or even this:

=B3*MIN(40,B4)

[Omia]

Try:

=B3*IF(B4>40,40,B4)

But I am not sure that is really the formula you want?

Do you want to only multiply anything above 40 hrs by a certain value, and anything under 40 by the regular rate?

If so, you need to supply more info about those rates and their respective locations...

To quote the instructions,
"Regular pay: Assume that Regular Pay ends at 40 hours. If the employee works more than 40 hours then the hours over 40 are overtime.

Overtime pay: If the employee works 40 hours or less then this will equal 0. If the employee works over 40 hours then the hours over 40 will be considered time and a half."

So now that I've got the regular pay down (thank you!) I've got to start working on the overtime pay formula. Any ideas?

[Omia]

**sorry for the double post**

Okay for the overtime formula I've worked out this:

=IF(B4<=40,0,B3*1.5*)

but after the pay rate times .5 I've no clue how to put "number of hours over 40". B4-40 maybe?

=IF(C4<=40,0,C3*1.5*(C4-40))

[rwgrietveld]

When you would like start counting from 40 on then

=if(c4<=40,0,c3*1.5*max(0,c4-40))

[NBVC]

Have a look here for working with Overtime in Excel

http://www.cpearson.com/excel/overtime.htm

[Omia]

Okay, thanks you guys for everything, but I have one last question.

How would I write a formula to display HIGHEST for the highest rate, and nothing for everything else? For example, say "John" has the highest salary, how could I create a formula to display the text 'highest' in his row ONLY?

[NBVC]

=IF(X1=Max($X$1:$X$100),"HIGHEST","")

where X1:X100 is the range of salaries to compare, and X1 is first...

[Omia]

okay so I've tried =IF(G5=MAX($D$5:$G$5),"HIGHEST","") with D5 being the smallest wage and G5 being the highest. However this is entering HIGHEST into all cells, I'm assuming because it's true G5 is the highest, therefor it's putting HIGHEST in all cells?

[NBVC]

Maybe you want?

=IF(G5=MAX($D5:$G5),"HIGHEST","")