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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

14​

[/TD]
[TD]

14​

[/TD]
[TD]

5​

[/TD]
[TD]

7​

[/TD]
[TD]

17​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

14​

[/TD]
[TD]

7​

[/TD]
[TD]

5​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

7​

[/TD]
[TD]

14​

[/TD]
[TD]

5​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

8​

[/TD]
[TD]

5​

[/TD]
[TD]

2​

[/TD]
[TD]

7​

[/TD]
[TD]

16​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

8​

[/TD]
[TD]

2​

[/TD]
[TD]

5​

[/TD]
[TD]

7​

[/TD]
[TD]

16​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

3​

[/TD]
[TD]

5​

[/TD]
[TD]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

16​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

3​

[/TD]
[TD]

5​

[/TD]
[TD]

8​

[/TD]
[TD]

16​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
</tbody>[/TABLE]


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.