Five cell conditional division test formula

[Chubster]

Hi,
I'm seeking assistance with the following:
I have five cells, each with a single whole number ranging from 2 through 50.
I'd like know if any of the five cells can be divided "equally" by one of the other four cells (no remainder).
If this tests true, then a 'test column would be set to value=1; else value=0.

In example:
a1=3; b1=5; c1=10; d1=13; e1=16 the test column would be set to value=1 as 10 can be divided by 5 evenly.

Kind Regards,
Chubs

 

[Marcelo Branco]

Hi Marcelo,
This worked great!
Before I close this chapter may I inquire if there's a way to add one more condition where set "True" only if the 14-7 are adjacent to each other?
In example: 1-14-5-3-7 should be "False" as 14-7 not adjacent. However; 1-14-7-5-3 would be "True" due to "14" and "7" are adjacent.
Thank you.

See pgc's post above - your first example is not correct (1 divides all numbers)

To consider only adjacent numbers maybe...

Array Formula in G1 copied down (adapted from pgc's formula)
=0+OR(IF(A1:E1<>TRANSPOSE(A1:E1),IF(ABS(COLUMN(A1:E1)-TRANSPOSE(COLUMN(A1:E1)))=1,MOD(A1:E1,TRANSPOSE(A1:E1))))=0)
Ctrl+Shift+Enter

	A​	B​	C​	D​	E​	F​	G​
1​	14​	14​	5​	7​	17​		0​
2​	3​	14​	7​	5​	7​		1​
3​	3​	7​	14​	5​	7​		1​
4​	8​	5​	2​	7​	16​		0​
5​	8​	2​	5​	7​	16​		1​
6​	3​	5​	8​	7​	16​		0​
7​	3​	5​	8​	16​	7​		1​

<tbody>
</tbody>

M.

 

[pgc01]

Hi

Assuming the 1 is a typo, try:

=SUMPRODUCT((MOD(A1:D1,B1:E1)*MOD(B1:E1,A1:D1)=0)*(A1:D1<>B1:E1))>0

 

[Marcelo Branco]

Hi

Assuming the 1 is a typo, try:

=SUMPRODUCT((MOD(A1:D1,B1:E1)*MOD(B1:E1,A1:D1)=0)*(A1:D1<>B1:E1))>0

Very nice!

But... as OP wants 1 or 0
=--(SUMPRODUCT((MOD(A1:D1,B1:E1)*MOD(B1:E1,A1:D1)=0)*(A1:D1<>B1:E1))>0)

M.

 

[Chubster]

Hi, yes you are correct.
My mistake...range is 2-50.
Thanks for the catch.