using 2 multi conditional if then statements based on value of another cell

    Thank you in advance for your help,


    I have searched thru the treads and tho there are a few threads related to multiple if then statements in one cell nothing seemed relavent or worked.


    I have a value of either 1 or -1 in column N


    I need to use formulas


    if(n3=-1,IF(Q3<D4,O3-D4,IF(P3>E4,N3-E4,0)
    if(N3=1,IF(Q3>E4,Q3-N3,IF(P3<D4,D4-N3,0)


    I have tried using OR between the two formulas but always get 0 returned,
    the formulas both work independently.


    Is there a function I am not aware of, or am I using the Or function wrong?

    Re: using 2 multi conditional if then statements based on value of another cell


    Duplicate - sorry, having problems with the forum.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: using 2 multi conditional if then statements based on value of another cell


    Try this: =IF(N3=-1,IF(Q3 < D4,O3-D4,IF(P3 > E4,N3-E4,IF(N3=1,IF(Q3 > E4,Q3-N3,IF(P3 < D4,D4-N3,0))))))

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: using 2 multi conditional if then statements based on value of another cell


    I have tried that formula as well. It returns a value of "false" when n3=-1 and criteria are not met. I need it to return 0 so all of my other formulas in the sheet will calculate correctly

    Re: using 2 multi conditional if then statements based on value of another cell


    Then try this, a bit long-winded but should work


    =IF(IF(N3=-1,IF(Q3 < D4,O3-D4,IF(P3 > E4,N3-E4,IF(N3=1,IF(Q3 > E4,Q3-N3,IF(P3 < D4,D4-N3,0)))))) =FALSE,0,IF(N3=-1,IF(Q3 < D4,O3-D4,IF(P3 > E4,N3-E4,IF(N3=1,IF(Q3 > E4,Q3-N3,IF(P3 < D4,D4-N3,0)))))))

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: using 2 multi conditional if then statements based on value of another cell


    Try this then: =IF(AND(N3=-1,Q3 < D4),O3-D4,IF(AND(N3=-1,P3 > E4),N3-E4,IF(AND(N3=1,Q3 > E4),Q3-N3,IF(AND(N3=1,P3 < D4),D4-N3,0))))

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: using 2 multi conditional if then statements based on value of another cell


    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    *

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: using 2 multi conditional if then statements based on value of another cell


    had to change a few cells around, but yes that did work, and I believe I understand the concept of why it was not working and why your formula does work.


    Thank you AliGW

    Re: using 2 multi conditional if then statements based on value of another cell


    Did you read and understand the messages about cross posting, both here and on the other forum? If you do it again, you are required to provide links to other places where you have asked the same question.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: using 2 multi conditional if then statements based on value of another cell


    I apologize, I did not know the forums were linked together, as this is my first day posting in these types of forums.

    Re: using 2 multi conditional if then statements based on value of another cell


    I guess OP did not understand ( let alone read) cross posting rules

    Using XL2003

    Re: using 2 multi conditional if then statements based on value of another cell


    Well, I expect the OP has now been prompted to read the rules agreed to upon joining, and has been enlightened! ;)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login