Removing "FALSE" result from IF Function

[itsmekarak]

I have a worksheet that has 4 columns: Per Piece, Hours, Extra, and Total
I am using an IF function that compares the wage earned on a per piece basis @ .25 each vs the hours+extra wage earned and then chooses which is greater.
Everything works fine with the function, however, when the entry is 0 in these columns, the function returns a "False" statement.

Is there a way to get rid of the "False" statement?

You can download the file here:

http://www.jowdy.com/uploads/Kara/If_Function_Error.zip

Thank you for your help!

 

[Jonmo1]

Can you post the actual formula, many people here can't/won't download files from public sharing sites.

 

[itsmekarak]

Yes of course...my apologies.

Column A = Per Piece
Column B = Hours
Column C = Extra
Column D = Total
Column E = Hidden minimum wage amount of 12.25

Column D Formula is: =IF((A2*0.25)<($E$1*B2),($E$1*(B2+C2)),IF(($E$1*(B2+C2))<(A2*0.25),(A2*0.25)))

Thank you for your help!

 

[Jonmo1]

OK, first we can remove alot of those (), most are not necessary..

=IF(A2*0.25 < $E$1*B2,$E$1*(B2+C2),IF($E$1*(B2+C2) < A2*0.25,A2*0.25))

Now you haven't really said what you want to happen instead of FALSE... Just "get rid of it"
The false is happening if Neither IF condition is true.
And the 2nd IF has no ValueIfFalse Argument, which would go here
=IF(A2*0.25 < $E$1*B2,$E$1*(B2+C2),IF($E$1*(B2+C2) < A2*0.25,A2*0.25,"whattodoinsteadofFALSEgoeshere"))

I would presume you just want the formula to return a blank.. ""

Try
=IF(A2*0.25 < $E$1*B2,$E$1*(B2+C2),IF($E$1*(B2+C2) < A2*0.25,A2*0.25,""))

 

[itsmekarak]

It worked! Now, is there any way I can factor in OT and DT into this same formula?

Row 5 (for those that DL the file), shows

Column A = 0
Column B = 8
Column C = 6
Column D = 171.50 (formula result)
Column E = 12.25 (hidden)

I would like to formula result to show OT pay (E1*1.5) for hours greater than 8 but less than 12
And DT pay (E1*2) for hours greater than 12.

Is this even possible?

The end result would be $220.50

(8 hours at $12.25 = $98 + 4 hours at $18.375 = $73.50 + 2 hours at $24.50 = $49.00)

Thank you for your help!

 

[konew1]

Here is a different approach, eliminating multiple levels of IF conditions and simply adding together the component parts of the earnings.

=MAX(A2*0.25,(B2<>"")*($E$1*B2+$E$1*1.5*MIN(C2,4)+$E$1*2*MAX(0,C2-4)))

 

[itsmekarak]

This is sooooo close and so over my head. lol But I noticed a couple of problems.

When you enter 1 into column B the end result is 12.25 as it should be.
If you enter in 9 into column B the end result is 110.25, 9 hours at 12.25. It should be 116.38 as it takes that 1 hour of OT over the 8 hour threshold into account.

Also, if I enter 1 into column B and then 1 into column C, the end result is 30.63. It should be 24.50, as 2 hours are less than the 8 hour threshold and not OT.

I have never worked with MIN and MAX before so I'm not real sure how it all works, but it appears as though it's calculating on the contents of each column instead of the combined total.
For clarity, the "Extra" column is nothing more than extra hours that one of our staff works over and above their normal scheduled shift. Because sometimes their shifts are only 5 hours long, the extra
column doesn't always mean OT. You have to combine the hours and extra columns together to get the true hours worked then apply the regular time, vs OT, vs DT rules. Is that possible?

My continued gratitude for your assistance!

 

[konew1]

The formula is based on column B holding the number of hours paid at standard rate ($12.25) and column C holds the number of hours to be paid at a higher rate. If column C value is more than 4 then the first 4 hours are paid at *1.5 and the additional hours (more than 4) are paid at *2.0.

Clearly this is not what you need. I will get back to you later

 

[itsmekarak]

I am so sorry! You're obviously super skilled and I have so appreciated your help.

 

[konew1]

Try this
=MAX(A2*0.25,(B2<>"")*($E$1*MIN(B2+C2,8)+$E$1*1.5*MIN(MAX(B2+C2-8,0),4)+$E$1*2*MAX(0,B2+C2-12)))
To explain the first red bit is to avoid errors if time in B is not entered (B2="")
The blue bit calculates the first 8 hours,
the next red bit calculates up to 4 *1.5 rate
finally any time over 12 hours at *2 rate