# Complex IF (AND (OR formula

• Hello - I'm trying to get the following formula to show either a single number (i.e. 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 nikolassor - that worked a treat! Thanks for your help :smile:

• 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!