is 3 a factor?

[MikeL]

Hi, I am using XL 2000.

I have a value in cell A1. In cell B1, I am trying to write a formula to determine if "3" is a factor of the value in cell A1. What could that formula be?

I was thinking of: If(A1/3."yes","no"), but I cant seem to put the correct equation after the division.

Thanks in advance.

 

[Yogi Anand]

Hi Barry:

Works fine by me ...

y030515h1.xls
	A	B	C	D	E
2		NumberToTest
3		3
4
5
6			Dave	Egress1
7		3	yes	yes	yes
8		3.33	yes	yes	yes
9		3.34	no	no	no
10		4	no	no	no
11		6	yes	yes	yes
12		12.666666	yes	yes	yes
13		9.999998	no	no	no
Sheet4

I think I see it -- in your post there is typo in the formula -- the formula in your post is ...

=IF(MOD(IF(MOD(AB7,1)=0,B7,B7*10^(LEN(MID(B7,FIND(".",B7),255))-1)),B$3)=0,"yes","no")

and the correct formula is ...

=IF(MOD(IF(MOD(B7,1)=0,B7,B7*10^(LEN(MID(B7,FIND(".",B7),255))-1)),$B$3)=0,"yes","no")

 

[Dave Patton]

You can revise the Lookup version so that it works OK with
integer numbers #-#=0 - 1 yields -1 so number should be just * 1 not 0


=GCD(A7*(LOOKUP(LEN(A7)-LEN(INT(A7))-1,{-99999,0,1,2,3,4,5,6;1,1,10,100,1000,10000,100000,10000000})),3)=3

 

[Barry Katcher]

O.K., Paddy D's formula seems to test out on the two different dividers in the two samples below. Now to figure out how to get a "yes" or "no" out of the TRUE or FALSE. I've got the second half figured out:

=IF((MOD(B7,$B$3)=0)=TRUE,"yes","no")

The first part I can't figure out. So far I've got this, which isn't working:

=IF((ISNUMBER(FIND(".",B7)),MOD((SUBSTITUTE(B7,".","")+0),$B$3)=0)=true,"yes","no")

And then, I'll have to combine the two. But that's for tomorrow, boys and goils. I'm off to la-la land. By the way, what ever happened to the OP?

Test with a value of 3

Keepers1.xls
	B	C	D	E	F
2	Numbertotest
3	3
4
5		Formula	by:
6		DavePatton	Egress1	Yogi	PaddyD
7	3	yes	yes	yes	TRUE
8	3.33	yes	yes	no	TRUE
9	3.34	no	no	no	FALSE
10	4	no	no	no	FALSE
11	6	yes	yes	yes	TRUE
12	12.666666	yes	yes	no	TRUE
13	9.999998	no	no	no	FALSE
Sheet1

Test with a value of 4

Keepers1.xls
	B	C	D	E	F
2	Numbertotest
3	4
4
5		Formula	by:
6		DavePatton	Egress1	Yogi	PaddyD
7	3	yes	yes	no	FALSE
8	3.33	yes	yes	no	FALSE
9	3.34	yes	yes	no	FALSE
10	4	yes	yes	yes	TRUE
11	6	yes	yes	no	FALSE
12	12.666666	no	no	no	FALSE
13	9.999998	no	no	no	FALSE
Sheet1

 

[Barry Katcher]

Dave and Yogi, I didn't see your last posts until after I had worked on and posted PaddyD's formula. I'll look at them tomorrow morning, I'm too exhausted now, it's nearly 2 a.m., and I am getting along in years :D

Thanks for all the work you've put in on this, and you, too, Paddy & Egress. And where IS the OP?

 

[MikeL]

Thanks for all the discussion on this topic. The original formula posted using the MOD function was all I needed as I am just working with integers. Its been fun to follow this thread and I've learned way more than I ever needed about math theory, formulas etc. Thanks again.

MikeL

 

[Barry Katcher]

Here's the result of my Omega-testing:

Two formulas to test whether various numbers (in column B) are evenly divisible by the test number (cell B3). Formulas are by Yogi Arnand and PaddyD of MrExcel. Tested O.K. with a dozen or so whole numbers, both positive and negative. Does not seem to work when "Number to test" is a decimal. For example, 3.33 returns "no" when it should return "yes" on the 3.33 figure in cell B8. But that's for another day.

Formulas in cells C7 & D7:
Yogi:
=IF(MOD(IF(MOD(B7,1)=0,B7,B7*10^(LEN(MID(B7,FIND(".",B7),255))-1)),B$3)=0,"yes","no")

PaddyD
=IF(IF(ISNUMBER(FIND(".",B7)),MOD((SUBSTITUTE(B7,".","")+0),$B$3)=0,MOD(B7,$B$3)=0)=TRUE,"yes","no")

Thanks, guys.

Keepers1.xls
	A	B	C	D	E
2		Numbertotest
3		3
4
5			Formula	by:
6			Yogi	PaddyD
7		3	yes	yes
8		3.33	yes	yes
9		3.34	no	no
10		4	no	no
11		6	yes	yes
12		12.666666	yes	yes
13		9.999998	no	no
14
Sheet1