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.

 

[Barry Katcher]

So, all that being taken into consideration, does anyone have any idea how to get 3.33 to return "yes" and 3.34 to return "no"? I've been beating my head against it for the past hour.

 

[Dave Patton]

Barry

You could make the number an integer

=GCD(A8*100,3)=3

with 3.33 this will give True; with 3.34 it will give False

 

[Egress1]

I don't think GCD is part of an add-in, like MoreFunc. I'm using Excel 2000 and I believe it's part of the standard Math/Trig functions.

But I just found the GCD doesn't work in all situations. For example, on 6.664, it returns a "yes", which is incorrect, since 3 doesn't go into 6.664 evenly. Still working on it.

How about this:
=IF(A1/3=INT(A1/3),"Yes","No")

Seems to work correctly

 

[Barry Katcher]

Hey, Dave and Egress. Both of your formulas, with a little tweaking to bring the multiplier up to 1,000,000, seems to work for numbers with up to 6 decimal places. Nice going, guys, thanks.

Book1
	A	B	C	D
1
2		Dave	Egress1
3	3	yes	yes
4	3.33	yes	yes
5	3.34	no	no
6	4	no	no
7	6	yes	yes
8	12.666666	yes	yes
9	9.999998	no	no
10
Sheet1

 

[Barry Katcher]

Well, guys, upon further Beta-testing, I came up with another glitch. The multiplier takes care of the numbers with decimals, but screws up on the ones without, or with fewer places than the multiplier provides for. At this rate, this looks like it will end up going into Omega-testing.

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

 

[Yogi Anand]

Hi Barry:

In this formulation, the formula in column D automatically looks at if i the values in column A have to be multiplied with whatever power of 10 that it needs to multiply them with and proceeds from there ...

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

 

[PaddyD]

oblique response:

you can turn the decimal portion of a number into a whole number with the following (amongst many others):

=RIGHT(MOD(A1,INT(A1)),LEN(MOD(A1,INT(A1)))-2)+0

paddy

 

[Barry Katcher]

Yogi, I altered your formula to refer to the test number in cell B3, and I'm getting false results in the 3.33 and 12.666666 row.

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

 

[Dave Patton]

Barry either of these should work

=GCD(--(INT(A6)&RIGHT(MOD(A6,INT(A6)),LEN(MOD(A6,INT(A6)))-2)+0),3)=3

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

-check the number of 0s

HTH Dave

 

[PaddyD]

how about this:

Book6
	B	C	D	E
1	Numtotest
2	3
3		Paddy
4	3	TRUE
5	3.33	TRUE
6	3.34	FALSE
7	4	FALSE
8	6	TRUE
9	12.66666	TRUE
10	9.999998	FALSE
Sheet3