# Removing "FALSE" result from IF Function

#### itsmekarak

##### New Member
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?

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Can you post the actual formula, many people here can't/won't download files from public sharing sites.

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

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

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:
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)

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

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!

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

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

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

Replies
1
Views
436
Replies
7
Views
945
Replies
2
Views
328
Replies
1
Views
430
Replies
1
Views
2K

1,221,218
Messages
6,158,595
Members
451,501
Latest member
andysacko

### 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.

### Which adblocker are you using?

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

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