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

 

[sadboy309]

You can use function MOD(x,5) to find the balance of X, if balance equal value 0 means 10 can be divided by 5 evenly.

=(MOD(A1,5)=0)*1

 

[Chubster]

Thank you for the response.
I tried this and it doesn't seem to work.
The range is a1:e1 and the values for any of the cells, in the range, can be 2 through 50.

Regards.

 

[Special-K99]

Assuming they are all integers, can't you just do this?

=IF(MOD(A1*B1*C1*D1*E1,5)=0,1,0)

or even

=NOT(MOD(A1*B1*C1*D1*E1,5))*1

 

[Marcelo Branco]

Difficult to conceive a solution with formulas.
Is a User Defined Function (UDF) acceptable?

M.

 

[Chubster]

Glad to try a UDF...thank you.

 

[Chubster]

I gave this a try but didnt work.
Regards.

 

[Special-K99]

@Chubster: Did my MOD formula work?

If not what figures are you using?

 

[Marcelo Branco]

Glad to try a UDF...thank you.

Maybe something like this

Paste this code in a VBA module

Function ChkMod(r As Range)
    Dim i As Long, j As Long
    
    ChkMod = 0
    For i = 1 To r.Count
        For j = 1 To r.Count
            If i <> j Then
                If r.Cells(i) Mod r.Cells(j) = 0 Or r.Cells(j) Mod r.Cells(i) = 0 Then
                    ChkMod = 1
                    Exit Function
                End If
            End If
        Next j
    Next i
End Function

Excel

	A​	B​	C​	D​	E​	F​	G​
1​	3​	5​	10​	13​	16​		1​
2​	3​	7​	10​	13​	16​		0​
3​	3​	7​	10​	14​	16​		1​
4​	3​	5​	11​	13​	44​		1​
5​	3​	5​	11​	13​	47​		0​
6​	3​	5​	11​	7​	47​		0​
7​	3​	5​	11​	7​	50​		1​

Formula in G1 copied down
=ChkMod(A1:E1)

Hope this helps

M.

 

[Chubster]

Apologies Special-K99. I did a reply incorrectly.
It did not work.