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

 

[XOR LX]

XOR
Is the MUNIT function only available in EXCEL 365?

Ciao, Marcelo. 2013, I believe.

Cheers

 

[pgc01]

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.

Hi Marcelo

I tried my 2 formulas with your data and got 7 with both.

This seems OK to me

2 divides 4
2 divides 6
2 divides 8
2 divides 16
4 divides 8
4 divides 16
8 divides 16

but maybe I'm missing something?

 

[Marcelo Branco]

Hi Marcelo

I tried my 2 formulas with your data and got 7 with both.

This seems OK to me

2 divides 4
2 divides 6
2 divides 8
2 divides 16
4 divides 8
4 divides 16
8 divides 16

but maybe I'm missing something?

Hi pgc

See post 1

Hi,
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.

I understood that OP wants either 1 or 0 as result

M.

 

[pgc01]

I understood that OP wants either 1 or 0 as result

M.

Hi Marcelo

Thank you, you are right. I was counting how many and the OP only wants to know if there is at least 1.

In that case,

=0+OR(IF(COLUMN(A1:E1)<>TRANSPOSE(COLUMN(A1:E1)),MOD(A1:E1,TRANSPOSE(A1:E1)))=0)

 

[Chubster]

Hi Marcelo, et al,
I tried the formula(s) and they work as requested (thank you!).
I discovered that I left out an additional important piece that I'm hoping can be retrofitted:
"Values in range A1:E1 that are the "equal" should not qualify as 'True"...they can be used in comparison to other non-equal values in the range.
In example: 2-4-7-3-7 should be false as "7's" are equal. However; 2-14-7-3-7 would be true due to "14" and "7".

Regards,
Chubs

 

[Marcelo Branco]

In example: 2-4-7-3-7 should be false as "7's" are equal. However; 2-14-7-3-7 would be true due to "14" and "7".

I think you meant 2-5-7-3-7 should be false. In your example should be true due to 4 and 2.

Try - i did a small adjustment in pgc's formula in post 24
=0+OR(IF(A1:E1<>TRANSPOSE(A1:E1),MOD(A1:E1,TRANSPOSE(A1:E1)))=0)
Ctrl+Shift+Enter

@pgc
Is the adjustment OK?

M.

 

[pgc01]

@pgc
Is the adjustment OK?

Perfect, Marcelo!

 

[Chubster]

Thanks Marcelo....and I did mess up with the 2-4
I'll give this a go.

Regards.

 

[Chubster]

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.

 

[pgc01]

Hi

That's a much more basic question.

You said in your post #1 that numbers range 2-50 and I see a 1 in your example.
Is there a new number range? 1 would divide all other numbers.