Multiple If, or, and

[aiki100]

Greetings all - I would like very much to get some assistance with the following please, if anyone has thoughts on it.


=IF(BB2="","",IF(AND($AR2="x",$AZ2="L",$AV2="C",J3> $BF2),K3-$BB2,IF(AND($AR2="x",$AZ2="L",$AV2="O",J3> $BF2),K3-$BB2,IF(AND($AR2="x",$AZ2="L",$AV2="B",J3> $BF2),I3-$BB2,IF(AND($AR2="x",$AZ2="S",$AV2="C",I3< $BF2),$BB2-K3,IF(AND($AR2="x",$AZ2="S",$AV2="O",I3< $BF2),$BB2-K3,IF(AND($AR2="x",$AZ2="S",$AV2="B",I3< $BF2),$BB2-K3,IF(AND($AR2="x",$AZ2="L",J3<= $BF2),$BF2-$BB2,IF(AND($AR2="x",$AZ2="S",I3>= $BF2),$BB2-$BF2,"")))))))))


This formula produces what is required, except for this: If "H" (which is an opening price) opens higher than the limit that "H" is not supposed to go beyond "I" (the limit), how could I handle that so that if this condition were met, the formula would return""? I also need the opposite addition, please.


Here is what I (think) would work — but I cannot figure out to how add this in to what I have above.


IF(AND($AR2="x",$AZ2="L",$AV2="O",H3< $J2),then blank,
IF(AND($AR2="x",$AZ2="S",$AV2="O",H3> $I2),then blank,


In other words, do all of the (longer) above formula, but if any of these two (above) conditions exist, plus that very first one, =IF(BB2="","") then return "".


Any help much appreciated, thank you so much!

 

[jtakw]

Hi,

In my sample below, F1 is the final formula according to your existing formula to include the 2 additional conditions as you described.

C1 is your original formula.
D1 is a shorter version of C1.
E1 is even shorter.
F1 is E1 formula plus adding the 2 new criterion.

I didn't test the formulas, since I didn't want to create all these (there's a bunch) dummy data.
Please test the formula to see if you're getting the correct results:

Book1
	C	D	E	F
1
Sheet49

Cell Formulas
Range	Formula
C1	=IF(BB2="","",IF(AND($AR2="x",$AZ2="L",$AV2="C",J3> $BF2),K3-$BB2,IF(AND($AR2="x",$AZ2="L",$AV2="O",J3> $BF2),K3-$BB2,IF(AND($AR2="x",$AZ2="L",$AV2="B",J3> $BF2),I3-$BB2,IF(AND($AR2="x",$AZ2="S",$AV2="C",I3< $BF2),$BB2-K3,IF(AND($AR2="x",$AZ2="S",$AV2="O",I3< $BF2),$BB2-K3,IF(AND($AR2="x",$AZ2="S",$AV2="B",I3< $BF2),$BB2-K3,IF(AND($AR2="x",$AZ2="L",J3<= $BF2),$BF2-$BB2,IF(AND($AR2="x",$AZ2="S",I3>= $BF2),$BB2-$BF2,"")))))))))
D1	=IF(BB2="","",IF(AND($AR2="x",$AZ2="L",OR($AV2={"C","O"}),J3> $BF2),K3-$BB2,IF(AND($AR2="x",$AZ2="L",$AV2="B",J3> $BF2),I3-$BB2,IF(AND($AR2="x",$AZ2="S",OR($AV2={"C","O","B"}),I3< $BF2),$BB2-K3,IF(AND($AR2="x",$AZ2="L",J3<= $BF2),$BF2-$BB2,IF(AND($AR2="x",$AZ2="S",I3>= $BF2),$BB2-$BF2,""))))))
E1	=IF(BB2="","",IF($AR2="x",IF(AND($AZ2="L",J3> $BF2),IF(OR($AV2={"C","O"}),K3-$BB2,IF($AV2="B",I3-$BB2,IF(AND($AZ2="S",OR($AV2={"C","O","B"}),I3< $BF2),$BB2-K3,IF(AND($AZ2="L",J3<= $BF2),$BF2-$BB2,IF(AND($AZ2="S",I3>= $BF2),$BB2-$BF2,""))))))))
F1	=IF(OR(BB2="",AND($AR2="x",OR($AZ2={"L","S"}),$AV2="O",OR(H3<$J2,H3>$I2))),"",IF($AR2="x",IF(AND($AZ2="L",J3> $BF2),IF(OR($AV2={"C","O"}),K3-$BB2,IF($AV2="B",I3-$BB2,IF(AND($AZ2="S",OR($AV2={"C","O","B"}),I3< $BF2),$BB2-K3,IF(AND($AZ2="L",J3<= $BF2),$BF2-$BB2,IF(AND($AZ2="S",I3>= $BF2),$BB2-$BF2,""))))))))

 

[aiki100]

Thank you jtakw!

I will certainly test and report back (tho it may be tomorrow by the time I can be thorough about it). I just wanted to thank you for the assist right away.

Question please: I see curly braces in some of the statements. Do I need to enter the whole thing as an array, or no? (trying to learn how your thinking went on this, so I can - hopefully - get smarter about this...)

So very much appreciated - Thanks again!

 

[jtakw]

You're welcome, no, this is Not an array formula.
The curly brackets houses the criteria for the OR function.
Enter as regular formula.

 

[aiki100]

just a quick edit and addition to the above:

I just did a (very quick) test, and without checking accuracy, I can see that all permutations are at least producing results (likely properly) except if AZ2="S", and AV2 is ether "C" or "O". These produce "FALSE." For some reason, the AZ2= "S" + AV2="B" combo works.



Any thoughts please?

 

[jtakw]

Hmmm, if the AZ2="S" and AV2="B" works, I'm wondering, how is "C" and "O" entered into AV2, manually or by formula, any possibility that there may be leading and/or trailing spaces?

 

[jtakw]

Ok, may be this will work, think I was missing a couple of "value if false":

Book1
	F
1
Sheet49

Cell Formulas
Range	Formula
F1	=IF(OR(BB2="",AND($AR2="x",OR($AZ2={"L","S"}),$AV2="O",OR(H3<$J2,H3>$I2))),"",IF($AR2="x",IF(AND($AZ2="L",J3> $BF2),IF(OR($AV2={"C","O"}),K3-$BB2,IF($AV2="B",I3-$BB2,IF(AND($AZ2="S",OR($AV2={"C","O","B"}),I3< $BF2),$BB2-K3,IF(AND($AZ2="L",J3<= $BF2),$BF2-$BB2,IF(AND($AZ2="S",I3>= $BF2),$BB2-$BF2,""))))),""),""))

Let me know if it works out.

 

[aiki100]

Hi

So yes, I found one trailing space - I have found that to get some long formulas to fully appear in the forum post, you sometimes have to add a space between an < or > and the following letter of number. (This per the forum FAQ.)

So I fixed that and I still get FALSE. (and yes, those C,O,B are manually entered...)

I am going to bet that your work is correct, and somehow, mine is wrong. (This is a very likely probability - lol)

So I will look at this more closely tomorrow (west coast, US), when I can learn better what you have put together, and what I have (likely) done wrong.

I will post results as I find them - thank you again VERY much for your help!

 

[aiki100]

[FONT=&quot]Hi there - morning…[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]So I now know what the issue is. You provided this (below), which worked, but was producing FALSE errors.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]=IF(OR(BB2="",AND($AR2="x",OR($AZ2={"L","S"}),$AV2="O",OR(H3<$J2,H3>$I2))),"",IF($AR2="x",IF(AND($AZ2="L",J3>$BF2),IF(OR($AV2={"C","O"}),K3-$BB2,IF($AV2="B",K3-$BB2,IF(AND($AZ2="S",OR($AV2={"C","O","B"}),I3<$BF2),$BB2-K3,IF(AND($AZ2="L",J3<=$BF2),$BF2-$BB2,IF(AND($AZ2="S",I3>=$BF2),$BB2-$BF2,""))))))))[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]And as predicted, the problem is me, not you.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]You may recall that in the original request, I said:[/FONT]
[FONT=&quot]
[/FONT]
If "H" (which is an opening price) opens higher than the limit that "H" is not supposed to go beyond "I" (the limit), how could I handle that so that if this condition were met, the formula would return""?




Well… it turns out that in the actual sample data I have, this limit, "I", or "J" are indeed sometimes breeched, and that's why it returns FALSE. I also discovered that it is not the return I would actually need. So I built the following to handle these issues:


[FONT=&quot]=IF(BB2="","",IF(AND($AR2="x",$AZ2="L",$AV2="C",H3<$J2),H3-$BB2,IF(AND($AR2="x",$AZ2="L",$AV2="O",H3<$J2),H3-$BB2,IF(AND($AR2="x",$AZ2="S",$AV2="C",H3>$I2),$BB2-H3,IF(AND($AR2="x",$AZ2="S",$AV2="O",H3>$I2),$BB2-H3,"")))))[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]I then attempted to merge this above with what you originally provided (but without duplicating the initial BB2="",""….)[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Whatever I did clearly does not work. Might you have a thought on how to merge these two sets of formula? [/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Many, many thanks again![/FONT]