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


    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.