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

 

[Chubster]

Thanks Marcelo.
This is what I was looking for.
Also, my appreciation to everyone else on this thread that took the time to respond.
Kind Regards,
Chubs

 

[Marcelo Branco]

You are welcome.

M.

 

[DanteAmor]

The following is an array formula


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:51.33px;" /><col style="width:51.33px;" /><col style="width:51.33px;" /><col style="width:51.33px;" /><col style="width:51.33px;" /><col style="width:51.33px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">3</td><td style="text-align:right; ">5</td><td style="text-align:right; ">10</td><td style="text-align:right; ">13</td><td style="text-align:right; ">16</td><td style="background-color:#92d050; text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">7</td><td style="text-align:right; ">10</td><td style="text-align:right; ">13</td><td style="text-align:right; ">16</td><td style="background-color:#92d050; text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">3</td><td style="text-align:right; ">7</td><td style="text-align:right; ">10</td><td style="text-align:right; ">14</td><td style="text-align:right; ">16</td><td style="background-color:#92d050; text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td style="text-align:right; ">5</td><td style="text-align:right; ">11</td><td style="text-align:right; ">13</td><td style="text-align:right; ">44</td><td style="background-color:#92d050; text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">3</td><td style="text-align:right; ">5</td><td style="text-align:right; ">11</td><td style="text-align:right; ">13</td><td style="text-align:right; ">47</td><td style="background-color:#92d050; text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">3</td><td style="text-align:right; ">5</td><td style="text-align:right; ">11</td><td style="text-align:right; ">7</td><td style="text-align:right; ">47</td><td style="background-color:#92d050; text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">3</td><td style="text-align:right; ">5</td><td style="text-align:right; ">11</td><td style="text-align:right; ">7</td><td style="text-align:right; ">50</td><td style="background-color:#92d050; text-align:right; ">1</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >F1</td><td >{=IF(SUMPRODUCT((IF(MOD(A1,A1:E1)=0,1,0))+(IF(MOD(B1,A1:E1)=0,1,0))+(IF(MOD(C1,A1:E1)=0,1,0))+(IF(MOD(D1,A1:E1)=0,1,0))+(IF(MOD(E1,A1:E1)=0,1,0)))=5,0,1)}</td></tr></table></td></tr></table>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[XOR LX]

Hi,

=N(SUM(N(IF(1-MUNIT(COLUMNS(A1:E1)),MOD(A1:E1,TRANSPOSE(A1:E1)))=0))>0)

committed as an array formula**.

Nice to get a chance to use MUNIT.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[pgc01]

For those of us without MUNIT() another option:

=SUM(--(MOD(IF(COLUMN(A1:E1)=TRANSPOSE(COLUMN(A1:E1)),0.5,A1:E1/TRANSPOSE(A1:E1)),1)=0))

confirmed with CTRL-SHIFT-ENTER

 

[Marcelo Branco]

Dante

Nice formula! Not too complicated and works.

I think it can be simplified
=IF(SUMPRODUCT((MOD(A1,A1:E1)=0)+(MOD(B1,A1:E1)=0)+(MOD(C1,A1:E1)=0)+(MOD(D1,A1:E1)=0)+ (MOD(E1,A1:E1)=0))=5,0,1)
confirmed with just Enter

XOR
Is the MUNIT function only available in EXCEL 365?
Never used. I'll take a look and learn how to use it.
Thanks for sharing

M.

 

[Marcelo Branco]

For those of us without MUNIT() another option:

=SUM(--(MOD(IF(COLUMN(A1:E1)=TRANSPOSE(COLUMN(A1:E1)),0.5,A1:E1/TRANSPOSE(A1:E1)),1)=0))

confirmed with CTRL-SHIFT-ENTER

Another!
And I said it was hard to come up with a formula ...

M.

 

[pgc01]

Using xor's logic (better than mine) I can simplify the formula, the same just not using MUNIT():

=SUM(--(IF(COLUMN(A1:E1)<>TRANSPOSE(COLUMN(A1:E1)),MOD(A1:E1,TRANSPOSE(A1:E1)))=0))

 

[Marcelo Branco]

pgc (Pedro )

I think your formula needs a small adjustment
=--(SUM(--(MOD(IF(COLUMN(A1:E1)=TRANSPOSE(COLUMN(A1:E1)),0.5,A1:E1/TRANSPOSE(A1:E1)),1)=0))>0)
Ctrl+Shift+Enter

to handle

[TABLE="class: grid"]
<tbody>[TR]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[TD]

6​

[/TD]
[TD]

8​

[/TD]
[TD]

16​

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


M.

 

[DanteAmor]

Another!
And I said it was hard to come up with a formula ...

M.

In the end several formulas came out