Solved: How can I have a nested IF-function combined with an OR and AND-fun

[Riaaz66]

Hi,

I am trying to get a nested IF-formula with an OR-function and a AND-function in it.
Basically want I want to have can only be explained if you open the attachment (see attachment "book1.xls"):

The value of the cells in column AW should contain the value of the cells of column E + K + AD + AG. So far no problem at all.
However, some of the rows (blue colored) does not contain the data that I need. SO therefor, I used the formula in column AV.
The value of the cells in column AV is depending/linked to the value of column AU.

Now here is the problem:
All the values in column E that contains "component" should get the also the currency, buy/sell and call/put value of it's parent (the above green colored row)
Now I have 2 types of components; PCC component and SWAP component. The SWAP component can be from "TRS Divs on Pay date"-type or "TRS Divs on Reset"-type.

I want excel to choose, between the length of the text in AV for the part of the 'TRS Divs Pay date" is 20 or if the length of the textpart "TRS Divs on Reset" is 17, that it should take in column AW the value of the cell in column AU + the Buy/Sell + Call/Put value.
I succeeded for 95%, however if you can see for eample in cell AW18, the value is "SWAP componentateEURB" while it should be "SWAP componentEURBuy".

This is the formula I have:
=IF(E5=" PCC component";AU5&MID(AV5;4;11);IF(AND((E5=" SWAP component");(LEN(MID(AV5;1;17))=LEN("TRS Divs on Reset")));AU5&MID(AV5;18;7);IF(AND((E5=" SWAP component");(LEN(MID(AV5;1;20))=LEN("TRS Divs on Pay date")));AU5&MID(AV5;21;7);AV5)))

Can someone please help me?

Thanks in advance and kind regards,

riaaz66

[Aussiebear]

Did you include in your starting position calculations for the Mid function, the fact that your true starting position should include the spaces in front of the string?

Other than that your sheet has protection so I'm unable to test this suggestion.

[Bob Phillips]

How about this

=IF(E5=" PCC component",AU5&MID(AV5,4,11),
IF(AND((E5=" SWAP component"),(LEN(MID(AV5,1,17))=LEN("TRS Divs on Reset"))),AU5&MID(AV5,FIND("EUR",AV5),99),
IF(AND((E5=" SWAP component"),(LEN(MID(AV5,1,20))=LEN("TRS Divs on Pay date"))),AU5&MID(AV5,FIND("EUR",AV5),99),AV5)))

[Riaaz66]

Hi Xld,

Your formula did work. Thank you very much for your efforts.
However, I don't understand why you inserted why you specifically "FIND for EUR" while there are also other currencies and then still the result is correct.

Besides that, with your formula I get a '#VALUE!'error" in a certain category of a product. (see cells AW84:AW103). I cannot solve this error.

Can you take a look again?

Thanks in advance.

[Bob Phillips]

Maybe that was just coincidence, and should be

=IF(E5=" PCC component",AU5&MID(AV5,4,11),
IF(AND((E5=" SWAP component"),(LEN(MID(AV5,1,17))=LEN("TRS Divs on Reset"))),AU5&MID(AV5,FIND(K5,AV5),99),
IF(AND((E5=" SWAP component"),(LEN(MID(AV5,1,20))=LEN("TRS Divs on Pay date"))),AU5&MID(AV5,FIND(K5,AV5),99),AV5)))

[Riaaz66]

Hi Xld,

The last solution worked perfectly. Forgot to thank you.
This post can be marked as solved.

Thanks again,

Riaaz66