Removing "FALSE" result from IF Function

itsmekarak

New Member
Joined
Sep 29, 2014
Messages
30
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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Can you post the actual formula, many people here can't/won't download files from public sharing sites.
 
Upvote 0
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!
 
Upvote 0
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,""))
 
Last edited:
Upvote 0
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!
 
Upvote 0
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)))
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top