Combine multiple IF functions

[gomes123]

Is there a way to combine these 4 IF functions into 1 function?

=IF(AND(E3>C3,G3>E3,F3>D3,H3>F3),"UP","NO")
=IF(AND(C3>E3,E3>G3,D3>F3,F3>H3),"DOWN","NO")
=IF(AND(E3>C3,G3>E3,D3>F3,F3>H3),"LEFT","NO")
=IF(AND(C3>E3,E3>G3,F3>D3,H3>F3),"RIGHT","NO")

The most I could do is write it like

=IF(OR(AND(E3>C3,G3>E3,F3>D3,H3>F3),AND(C3>E3,E3>G3,D3>F3,F3>H3),AND(E3>C3,G3>E3,D3>F3,F3>H3),AND(C3>E3,E3>G3,F3>D3,H3>F3)),"YES","NO")

But the problem I need it to state specificially (UP/DOWN/LEFT/RIGHT) but the code above could only output values values is true/false (YES/NO).

Thanks!

 

[StephenCrump]

Try:

=IF(AND(E3>C3,G3>E3,F3>D3,H3>F3),"UP",IF(AND(C3>E3,E3>G3,D3>F3,F3>H3),"DOWN",IF(AND(E3>C3,G3>E3,D3>F3,F3>H3),"LEFT",IF(AND(C3>E3,E3>G3,F3>D3,H3>F3),"RIGHT","NO"))))

Or a little more succinctly, and easier to extend ....

=IFERROR(INDEX({"Up","Down","Left","Right"},MATCH(SIGN(E3-C3)*1000+SIGN(G3-E3)*100+SIGN(F3-D3)*10+SIGN(H3-F3),{1111,-1111,1089,-1089},)),"No")

 

[gomes123]

Try:

=IF(AND(E3>C3,G3>E3,F3>D3,H3>F3),"UP",IF(AND(C3>E3,E3>G3,D3>F3,F3>H3),"DOWN",IF(AND(E3>C3,G3>E3,D3>F3,F3>H3),"LEFT",IF(AND(C3>E3,E3>G3,F3>D3,H3>F3),"RIGHT","NO"))))

Or a little more succinctly, and easier to extend ....

=IFERROR(INDEX({"Up","Down","Left","Right"},MATCH(SIGN(E3-C3)*1000+SIGN(G3-E3)*100+SIGN(F3-D3)*10+SIGN(H3-F3),{1111,-1111,1089,-1089},)),"No")

Thanks! It works perfectly!

I like your 2nd code (IFERROR), never thought of it that way, very elegant! Could you care to explain what the digits 1111,-1111,1089,-1089 mean, and how does it work? Does it matter if its 1111, and not say 2222 etc?

 

[StephenCrump]

Could you care to explain what the digits 1111,-1111,1089,-1089 mean, and how does it work?

For two numbers a and b, you are interested in the two results b>a and a>b, i.e. SIGN(b-a) =1 or -1.

For the four sets of two numbers, the results you're interested in are:

	A	B	C	D	E
1	Sign 1	Sign 2	Sign 3	Sign 4	Result
2	1	1	1	1	Up
3	-1	-1	-1	-1	Down
4	1	1	-1	-1	Left
5	-1	-1	1	1	Right
Sheet1

To make the comparison easier, we can multiply by powers of ten to make a single number:

	A	B	C	D	E	F
1	Sign 1	Sign 2	Sign 3	Sign 4	Result
2	1	1	1	1	Up
3	-1	-1	-1	-1	Down
4	1	1	-1	-1	Left
5	-1	-1	1	1	Right
6
7	1000	100	10	1	Up	1111
8					Down	-1111
9					Left	1089
10					Right	-1089
Sheet1

Cell Formulas
Range	Formula
F7:F10	F7	=SUMPRODUCT(A2:D2,A$7:D$7)

 

[gomes123]

can multiply by powers of ten to make a single number:

Thanks for the explanation! Very intuitive and good way to go about it.

How did you come about to think of "multiplying it by powers of 10"? Is this a "best practices" method that is commonly used when using SUMPRODUCT or SIGN?

 

[StephenCrump]

How did you come about to think of "multiplying it by powers of 10"?

Using powers of some number N (not necessarily 10) is a technique that sometimes comes in handy in Excel formulae.

I wouldn't describe it as best practice, or something particular to SUMPRODUCT or SIGN, rather it's just a technique that is sometimes useful.

 

[gomes123]

Using powers of some number N (not necessarily 10) is a technique that sometimes comes in handy in Excel formulae.

I wouldn't describe it as best practice, or something particular to SUMPRODUCT or SIGN, rather it's just a technique that is sometimes useful.

Thanks for the explanation, that's a really useful technique that I've learnt now, most appreciated! I like the innovative way of thinking!