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

[Table="class: grid"][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]

3​

[/td][td]

5​

[/td][td]

10​

[/td][td]

13​

[/td][td]

16​

[/td][td][/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

3​

[/td][td]

7​

[/td][td]

10​

[/td][td]

13​

[/td][td]

16​

[/td][td][/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

3​

[/td][td]

7​

[/td][td]

10​

[/td][td]

14​

[/td][td]

16​

[/td][td][/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

3​

[/td][td]

5​

[/td][td]

11​

[/td][td]

13​

[/td][td]

44​

[/td][td][/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

3​

[/td][td]

5​

[/td][td]

11​

[/td][td]

13​

[/td][td]

47​

[/td][td][/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

3​

[/td][td]

5​

[/td][td]

11​

[/td][td]

7​

[/td][td]

47​

[/td][td][/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

3​

[/td][td]

5​

[/td][td]

11​

[/td][td]

7​

[/td][td]

50​

[/td][td][/td][td]

1​

[/td][/tr]
[/table]

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.