Five cell conditional division test formula

C

Chubster

New Member
Joined
Apr 28, 2019
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
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
 
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
pgc01 said:
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
Click to expand...

Another!
And I said it was hard to come up with a formula ...:oops: ;)

M.
 
Upvote 0
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))
 
Last edited:
Upvote 0
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

2​
4​
6​
8​
16​

<tbody>
</tbody>


M.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Excel formula does not return expected result when encountering a cell that includes text in a formula reference
2
Replies
11
Views
618
Joe4
Joe4
M
  • Question
return multiple cell values based on multiple criteria
Replies
1
Views
495
mumps
mumps
J
  • Question
Dividing table into two tables with equal number of columns
Replies
1
Views
409
JimS63
J
C
  • Question
Nearest whole number to divide by to be near a value
2
Replies
16
Views
977
Fluff
Fluff
E
  • Solved
Extract cell content into separate columns
Replies
3
Views
473
estephenkim
E

Forum statistics

Threads
1,214,981
Messages
6,122,566
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top