Complex IF (AND (OR formula

    Hello - I'm trying to get the following formula to show either a single number (i.e. 8) or, where there is a variance (above/below 2/3) show a number with the variance (i.e. 6(2) ):



    IF(AND(D2>2,D3<3,OR(D2<3,D3>2)),(D2+D3)&"("&(D2-D3)&")",(D2+D3))



    Sadly, the results for D2=4, D3=4 are 8(0) where the result should be 8


    If I use the same formula but swap the true and false statements (IF(AND(D2>2,D3<3, OR(D2<3,D3>2)), (D2+D3), (D2+D3)&"("&(D2-D3)&")") the brackets disappear, but even if D2=3 and D3=2 - which should show 5(1) :duh:


    I have no idea what I've done wrong so any help would be hugely appreciated!
    [INDENT]
    PS the range in each cell (i.e. D2) is 1-4 which results with one of the following: 8, 7, 6, 6(2), 5(1), 5(3), 4, 2, 1
    [/INDENT]

    Re: Complex IF (AND (OR formula


    I think you first have to put the AND inside OR.
    Try this:
    IF(OR(AND(D2>2,D3<3),AND(D2<3,D3>2)),D2+D3&"("&D2-D3&")",(D2+D3))

    Re: Complex IF (AND (OR formula


    Is this what you mean?


    =IF(D2=D3,D2+D3,D2+D3&"("&ABS(D2-D3)&")")


    Quote

    the range in each cell (i.e. D2) is 1-4 which results with one of the following: 8, 7, 6, 6(2), 5(1), 5(3), 4, 2, 1


    There cannot be a result of 1 because that would mean either D2 or D3 would have to be 0 and the other cell 1, also values of 4 and 3 has a variance of 1 so result would be 7(1), or am I missing something?

    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: Complex IF (AND (OR formula


    Or, this will give you the range of results you specify, but not a result of 1 which can never be possible if D2 and D3 have a value between 1 and 4.


    =IF(D2=D3,D2+D3,IF(OR(D2+D3=5,D2+D3=6),D2+D3&"("&ABS(D2-D3)&")",D2+D3))

    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: Complex IF (AND (OR formula


    Hi all, further to the above I'm having issues again:


    If one cell is blank I need the result to be "No value" when the 2 cells are added (D2+D3). It worked in my test sheet [where results were errors from blanks, I just added IFERROR = "No value"] but NOT with the live data [where it shows 3(3) if one cell is 3 and the other blank].


    The other 'issue' (though not stopping my data from being used) is the fact that some of the results [5(1)] are coming up with negatives [5(-1)] -for obvious reasons... is there any way to stop this? I've adjusted my COUNTIF [COUNTIS(E3:P146,"=5(1)")+COUNTIFS(E3:P146,"=5(-1)")] to account for both (1) and (-1) but wondering if there's a slicker way to get this done?


    Thanks again!

    Re: Complex IF (AND (OR formula


    Did you actually try the second formula I posted? Both my formulas would have not recorded a result of [5(-1)] but would have resulted in [5(1)] even if D3 is larger than D2.


    To modify nikolassor's formula try


    IF(OR(AND(D2>2,D3<3),AND(D2<3,D3>2)),D2+D3&"("&ABS(D2-D3)&")",(D2+D3))

    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: Complex IF (AND (OR formula


    Hi KjBox - apologies for missing that as IT WORKED :thanx: cheers for your help and reposting one of the solutions, it is appreciated!

    Re: Complex IF (AND (OR formula


    You're welcome.

    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.

Participate now!

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

Register Yourself Login