IF Statement - Help

[tlc53]

Hi there,

I have an IF statement which I'm almost there with, but I can't get the last part to work..

=IF(F45=0,"",IF(OR(D8="Company"}),"621/783",IF(OR(D8="Partnership"},F45>0),F14,I14),IF(F45>0),"511/001","521/001")))

So it's the last IF statement which I can't get to work. IF(F45>0),"511/001","521/001"))). So basically, if it's not Company or Partnership and F45>0, return 511/001, F45<0 return 521/001.

Thank you!

 

[Matty]

Hi,

Your formula looks, syntactically, a little weird.

Could you share a short example to illustrate what you're trying to do?

Matty

 

[tlc53]

If f45 equals 0, return blank.
If d8 equals Company return, 621/783
If d8 equals Partnership and F45>0, return F14 but if F45<0 return I14
If d8 does not equal Company or Partnerhsip and F45 >0, return "511/001" but if F45 <0 return "521/001".

It's a formula to automate coding

 

[Matty]

Sounds like:

=IF(F45=0,"",IF(D8="Company","321/783",IF(AND(D8="Partnership",F45>0),F14,IF(AND(D8="Partnership",F45<0),I14,IF(AND(OR(D8<>{"Company","Partnership"}),F45>0),"511/001","521/001")))))

Might be possible to shorten it a little, but it should work. I've also made the assumption that "521/001" is the default if none of the proceeding arguments are met. The formula will need tweaking if this is not the case.

Matty

 

[tlc53]

Thanks Matty! That seems to have done the trick.
Appreciate your help!

 

[tlc53]

Not sure if you're still awake, but any chance you can tell me where I'm going wrong in this formula..

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

 

[tlc53]

Ah-ha! I worked it out. Brackets in the wrong place

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

 

[Matty]

Ah-ha! I worked it out. Brackets in the wrong place

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

Glad you're sorted.

Note that the curly brackets {} are superfluous in the above formula - they're only needed when passing an array, i.e. more than one value.

Matty