# Trouble with formula not finding its logical expression

##### Well-known Member
Hello again.

I Have a little formula, (when I say little I mean long) as below

=IF(AND(DropList!\$B\$96=1,BE10>0),AZ10+(IF(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6<\$E\$72,0,IF(\$E\$72<\$E\$74,MAX(0,(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72-(\$E\$74-\$E\$72)))*2+(IF((\$H\$32-\$E\$72)<(\$E\$74-\$E\$72),(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72),(\$E\$74-\$E\$72)))*1.5,(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72)*2))*\$FY10+IF(DropList!\$B\$70=TRUE,\$BM10,IF(DropList!\$B\$96=2,BP10/(\$E\$85),IF(AND(DropList!\$B\$96=3,BE10>0),AZ10+((BP10/(\$E\$85)/\$L\$61)),"")))))

When I test the logical expressions the fisrt argument (DropList!\$B\$96=1,BE10>0), it true value consist of the remiander of the equation,

In reality I need the false argument to commence from IF(DropList!\$B\$96=2 and the third argument from (DropList!\$B\$96=3,BE10>0)

can any one offer solution to manage the syntax so that the argument break up in the right way and return values instead of TRUE/FALSE?

I'm sure it could be written more economical, but its beyond me at the moment. it basically is a wages counter.

Thanks as always
johnny

### 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).
If simplified, your formula looks like this:

=IF(Test1,IF(Test2,True2,IF(Test3,True3+IF(Test4,True4,False4),False3))+IF(Test5,True5,IF(Test6,True6,IF(Test7,True7,False7))))

True1 is covered by the remainder of the formula. False2 is covered by Test3, False5 is covered by Test6 and False6 is covered by Test7. False1 is missing.

Do you want?

=IF(Test1,IF(Test2,True2,IF(Test3,True3+IF(Test4,True4,False4),False3))+IF(Test5,True5,0),IF(Test6,True6,IF(Test7,True7,False7)))

False5 is now covered by the zero and False1 is covered by Test6.

Converting your formula, I think it would be:

=IF(AND(DropList!\$B\$96=1,BE10>0),AZ10+(IF(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6<\$E\$72,0,IF(\$E\$72<\$E\$74,MAX(0,(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72-(\$E\$74-\$E\$72)))*2+(IF((\$H\$32-\$E\$72)<(\$E\$74-\$E\$72),(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72),(\$E\$74-\$E\$72)))*1.5,(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72)*2))*\$FY10+IF(DropList!\$B\$70=TRUE,\$BM10,0),IF(DropList!\$B\$96=2,BP10/(\$E\$85),IF(AND(DropList!\$B\$96=3,BE10>0),AZ10+((BP10/(\$E\$85)/\$L\$61)),""))))

Yes False One is missing, Ideally I need false One to comence from IF(DropList!\$B\$96=2. (the second false is IF(AND(DropList!\$B\$96=3,BE10>0) which is Test 6- I think)
the basic premise of the argument is depending on wether DropList!\$B\$96 = 1,2 or 3 will give the different result, and that only these results are to be displayed if Argument one is true ie IF(AND(DropList!\$B\$96=1,BE10>0)

Thank you for looking at it and I hope it helps you to help me solve this.

I did try your solution and try to manipulate it but was still not getting the result I intended.

Its a little conveluted formula which doesn't help with clarity.

johnny

In the formula I posted IF(DropList!\$B\$96=2 is the False argument for IF(AND(DropList!\$B\$96=1,BE10>0). So it should give you the result you intended. It's difficult to test without some sample data and required results.

Thanks Andrew,
its weird I have pasted directly your amended formula into the cell and test the logic on it still comes up with the remainder of the equation as the TRUE argument for IF(AND(DropList!\$B\$96=1,BE10>0).

=IF(AND(DropList!\$B\$96=1,BE10>0),AZ10+(IF(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6<\$E\$72,0,IF(\$E\$72<\$E\$74,MAX(0,(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72-(\$E\$74-\$E\$72)))*2+(IF((\$H\$32-\$E\$72)<(\$E\$74-\$E\$72),(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72),(\$E\$74-\$E\$72)))*1.5,(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72)*2))*\$FY10+IF(DropList!\$B\$70=TRUE,\$BM10,0),IF(DropList!\$B\$96=2,BP10/(\$E\$85),IF(AND(DropList!\$B\$96=3,BE10>0),AZ10+((BP10/(\$E\$85)/\$L\$61)),""))))

DropList!\$B\$96 = User entered 1,2 or 3 (basis for three true outcomes
BE10 = days worked, ie not 0 days
H32 =Number of hours worked (eg 12)
E72 = base hour of a standard day (ie before overtime) (eg 8 hours)
E74 = When doubel time starts (ie differ between E72 is at x1.5) (eg 12-8 hours)
DropList!\$B\$70=TRUE is a check box
BP10 = and alternative amount (eg 200)

I know that the main formula works:
AZ10+(IF(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6<\$E\$72,0,IF(\$E\$72<\$E\$74,MAX(0,(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72-(\$E\$74-\$E\$72)))*2+(IF((\$H\$32-\$E\$72)<(\$E\$74-\$E\$72),(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72),(\$E\$74-\$E\$72)))*1.5,(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72)*2))*\$FY10+IF(DropList!\$B\$70=TRUE,\$BM10,0)

its just when I integrate this as part of the three arguments that it stumbles.

Hope this hasn't confused things more!

-(on a side note the cells on the formula do not change colour when you click in the formula itself, only their brackets change colour - Is there a reason for this, or does this mean anything?)

Sorry Andrew for the overload.

johnny

Thanks Andrew,
its weird I have pasted directly your amended formula into the cell and test the logic on it still comes up with the remainder of the equation as the TRUE argument for IF(AND(DropList!\$B\$96=1,BE10>0).

=IF(AND(DropList!\$B\$96=1,BE10>0),AZ10+(IF(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6<\$E\$72,0,IF(\$E\$72<\$E\$74,MAX(0,(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72-(\$E\$74-\$E\$72)))*2+(IF((\$H\$32-\$E\$72)<(\$E\$74-\$E\$72),(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72),(\$E\$74-\$E\$72)))*1.5,(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72)*2))*\$FY10+IF(DropList!\$B\$70=TRUE,\$BM10,0),IF(DropList!\$B\$96=2,BP10/(\$E\$85),IF(AND(DropList!\$B\$96=3,BE10>0),AZ10+((BP10/(\$E\$85)/\$L\$61)),""))))

DropList!\$B\$96 = User entered 1,2 or 3 (basis for three true outcomes
BE10 = days worked, ie not 0 days
H32 =Number of hours worked (eg 12)
E72 = base hour of a standard day (ie before overtime) (eg 8 hours)
E74 = When doubel time starts (ie differ between E72 is at x1.5) (eg 12-8 hours = 4 hours overtime)
DropList!\$B\$70=TRUE is a check box
BP10 = and alternative amount (eg 200)

I know that the main formula works:
AZ10+(IF(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6<\$E\$72,0,IF(\$E\$72<\$E\$74,MAX(0,(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72-(\$E\$74-\$E\$72)))*2+(IF((\$H\$32-\$E\$72)<(\$E\$74-\$E\$72),(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72),(\$E\$74-\$E\$72)))*1.5,(INT(\$H\$32)+(\$H\$32-INT(\$H\$32))/0.6-\$E\$72)*2))*\$FY10+IF(DropList!\$B\$70=TRUE,\$BM10,0)

its just when I integrate this as part of the three arguments that it stumbles.

Hope this hasn't confused things more!

-(on a side note the cells on the formula do not change colour when you click in the formula itself, only their brackets change colour - Is there a reason for this, or does this mean anything?)

Sorry Andrew for the overload.

johnny

Replies
6
Views
103
Replies
5
Views
152
Replies
10
Views
498
Replies
1
Views
138
Replies
3
Views
414

1,219,892
Messages
6,150,814
Members
450,985
Latest member
Andynair7

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

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