IF/AND/OR Statement

[tlc53]

Hi there,

=IF(AND(OR($D$8="Trust - Business",$D$8="Company"),$F$47>0),"494"),IF($F$47>0,"465/"&LEFT($D$28,3),)

So if D8 = "Trust - Business" or "Company" and F47 >0, return "494", but if it doesn't equal "Trust - Business" or "Company" and F47 >0 return "465/" and then first 3 characters of D28. If F47 is not more than zero, return blank.

I can kind of see where I'm going wrong but not sure how to fix it. If F47>0 then both statements are true.

Can someone shed some light please? Thank you

 

[Scott Huish]

=IF(AND(OR(D8={"Trust-Business","Company"}),F47>0),494,IF(F47>0,465&LEFT(D28,3),""))

 

[tlc53]

Thanks for that!! Works perfectly!

 

[tlc53]

Hi again,

Would you be able to help me extend the formula further..

Same criteria but if F47 less than zero, return different values. So basically a combination of these two codes;

=IF(AND(OR(D8={"Trust - Business","Company"}),F47>0),494,IF(F47>0,"465/"&LEFT(D28,3),""))
=IF(AND(OR(D8={"Trust - Business","Company"}),F47<0),474,IF(F47<0,"495/"&LEFT(D28,3),""))

And if equals 0, return blank. I've worked out it's not as simple as just adding them together.

Thank you!!

 

[Scott Huish]

=IF(F47=0,"",IF(OR(D8={"Trust - Business","Company"}),IF(F47>0,494,474),IF(F47>0,"465/","495/")&LEFT(D28,3)))

 

[tlc53]

Awesome! Thank you.
It makes sense now that I see it, and how you deal with the zero return first.
You've helped me out heaps. Thanks!