Formula modification help

[Ian Betteridge]

Morning All,
Can anyone please help with changing this formula....
=IF(A2="","",OR(AND(B2=Sheet2!$F$2,K2>=1),AND(B2=Sheet2!$F$3,K2>=8))+0)

I need the 1 in above formula changing if (as below)

=IF(A2="","",OR(AND(B2=Sheet2!$F$2,K2>= ? ),AND(B2=Sheet2!$F$3,K2>=8))+0)

IF H2 is less than 40725 then ? equal to 1
IF H2 is equal to or greater than 40725 then ? equal to 0.16666

Any help is much appreciated.
Regards Ian

 

[repairman615]

Hello,

I am not able to test fully... would this do:

=IF(A2="","",OR(AND(B2=Sheet2!$F$2,K2>=IF(H2<40725,1,0.16666)),AND(B2=Sheet2!$F$3,K2>=8))+0)

 

[Ian Betteridge]

Thanks, that bit works fine.
Could I expand from that a little............

Currently.. =IF(A2="","",OR(AND(B2=Sheet2!$F$2,K2>=IF(H2<40725,1,0.16666)),AND(B2=Sheet2!$F$3,K2>=8))+0)

I need the 8 in above formula changing if (as below)

=IF(A2="","",OR(AND(B2=Sheet2!$F$2,K2>=IF(H2<40725,1,0.16666)),AND(B2=Sheet2!$F$3,K2>= ? ))+0)


IF H2 is less than 40725 then ? equal to 8
IF H2 is equal to or greater than 40725 then ? equal to 1

I've got this but it ain't working?
=IF(A2="","",OR(AND(B2=Sheet2!$F$2,K2>=IF(H2<40725,1,0.16666)),AND(B2=Sheet2!$F$3,K2>= IF(H2<40725,8,1))+0)

Regards Ian

 

[Ian Betteridge]

Anyone tell me why this retuns "FALSE" and not a 1 or 0.
=IF(A2="","",OR(AND(B2=Sheet2!$F$2,K2>=IF(H2<40725,1,0.16666)),AND(B2=Sheet2!$F$3,K2>=IF(H2<40725,8,1))+0))

Thanks Ian

 

[repairman615]

Hello,

I think there may be a misplaced paren..
(This would also explain why your formula did not evaluate to a true+0=1 or a false+0=0)
try:

=IF(A2="","",OR(AND(B2=Sheet2!$F$2,K2>=IF(H2<40725,1,0.16666)),AND(B2=Sheet2!$F$3,K2>=IF(H2<40725,8,1 )))+0)

 

[Ian Betteridge]

Thanks very much.
Superb