IF Statement - Help

tlc53

Active Member
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi,

Your formula looks, syntactically, a little weird.

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

Matty

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

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

Thanks Matty! That seems to have done the trick.

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)))

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)))))

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)))))

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:

Replies
6
Views
602
Replies
4
Views
165
Replies
1
Views
214
Replies
0
Views
245
Replies
2
Views
510

1,196,426
Messages
6,015,187
Members
441,882
Latest member
LostinExcelHelp

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.

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

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