Multiple IF AND OR

[aiki100]

Hello -


Thanks for taking a look at this - I have been trying to get this right, and I keep on getting an error. I know it must be in the parenthesis syntax (at a minimum), but I am missing something...any help will be very much appreciated!


This formula works fine:


=IF(OR(AND($Z3="B",$AD3<$AC3),AND($Z3="S",$AD3>$AC3)), ($AC3-$AD3)/$E3, ($AD3-$AC3)/$E3)


What I am trying to do is add the following, which is (sadly) not fine: It is the same as the line above, but with an additional two sets of AND criteria, both of which would return a negative number if the criteria is satisfied.I.e., so ONE of these criteria will be TRUE.


=IF(OR(AND($Z3="B",$AD3<$AC3),AND($Z3="S",$AD3>$AC3), AND($Z3="B",$AD3>$AC3), AND($Z3="S",$AD3<$AC3))), ($AC3-$AD3/$E3), ($AD3-$AC3/$E3),($AC3-$AD3/$E3*-1),($AD3-$AC3/$E3)*-1)


Thanks again for any help -

 

[Nishant94]

Re: Multiple IF AND OR help, thanks!

Maybe you want something like this:

=IF(AND($Z3="B",$AD3<$AC3),($AC3-$AD3)/$E3,
                 IF(AND($Z3="S",$AD3>$AC3),($AD3-$AC3)/$E3,
                      IF(AND($Z3="B",$AD3>$AC3),($AC3-$AD3)/$E3*(-1),
                           IF(AND($Z3="S",$AD3<$AC3),($AD3-$AC3)/$E3*(-1),""))))

 

[aiki100]

Re: Multiple IF AND OR help, thanks!

Maybe you want something like this:

=IF(AND($Z3="B",$AD3<$AC3),($AC3-$AD3)/$E3,
                 IF(AND($Z3="S",$AD3>$AC3),($AD3-$AC3)/$E3,
                      IF(AND($Z3="B",$AD3>$AC3),($AC3-$AD3)/$E3*(-1),
                           IF(AND($Z3="S",$AD3<$AC3),($AD3-$AC3)/$E3*(-1),""))))

Hello Nisant!
Thank you SO much for taking this on, I really appreciate it. I especially like the way you wrote it out: I had not thought of this before, and it makes it so much easier to see what's going on.

So, this formula produces no error now, but is still missing something: All criteria are returning positive numbers. the last two, if either are satisfied, should produce a negative number. I can see the *(-1) in there, which is what I thought would work, but it doesn't seem to. Have you any thoughts on this? I think we are 98% there...

Thank you again for you effort and willingness to help with this. I hope your day fine indeed...

J

 

[Bodin]

Re: Multiple IF AND OR help, thanks!

=IF(AND($Z3="B",$AD3<$AC3),($AC3-$AD3)/$E3,
IF(AND($Z3="S",$AD3>$AC3),($AD3-$AC3)/$E3,
IF(AND($Z3="B",$AD3>$AC3),($AC3-$AD3)/$E3,
IF(AND($Z3="S",$AD3<$AC3),($AD3-$AC3)/$E3,""))))

I think now it will work

 

[Bodin]

Re: Multiple IF AND OR help, thanks!

if AC-AD is negative, (AD>AC) then when u multiply with -1 u get +(-*-=+)
If i understand well what u 2 wanna to do

 

[aiki100]

Re: Multiple IF AND OR help, thanks!

Bodin - Your code worked. Awesome!

Thank you for the help (and for the explanation - which is even more important, so I can continue try to learn this stuff better as I go).

All the answers to the post were really helpful (because I learned things from both of them), and I want to thank you all...!

Thanks again, Bodin -