tlc53

Active Member
Joined
Jul 26, 2018
Messages
399
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!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi,

Your formula looks, syntactically, a little weird.

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

Matty
 
Upvote 0
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
 
Upvote 0
Sounds like:

Code:
=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
 
Upvote 0
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)))
 
Upvote 0
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)))))
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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?

Disable AdBlock

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
Back
Top