Solved: Highest Non trivial Factor (Divisor)

[mr.why]

Hello from a newbie.

Before i set to work writing a custom function (which i may need help on later) is there a build in function to determine the greatest divisor of a number (excluding the number itself) so:

20 would return 10
4 would return 2
15 would return 5
17 would return 1
etc....

Hope i have made myself clear on that please feel free to tell me if i have missed anything out.

[Andy Pope]

Array formula. Commit use CTRL+SHIFT+ENTER


=MAX(IF(MOD(A1,ROW(INDIRECT("2:" & A1)))=0,A1/ROW(INDIRECT("2:" & A1)),0))

Are you sure about not including the number itself.
17=1 would suggest otherwise.

[SamT]

Andy,

Whaa!!!

How you do that? Indirect(2:Number)???

It works. At least up to Number = 65536.

Is this some undocumented "feature" of Indirect?

Inquiring minds. . . .

SamT

[Bob Phillips]

It is a very common technique where ROW(INDIRECT is used to create an array of numbers that can be evaluated one by oine within an array formula.

If say A1 = 27 and you evaluate ROW(INDIRECT("2:" & A1)) you get

{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27}

which is every value betwen 2 and the number itself. This array is passed to the MOD function to see which divide exactly into the number,

MOD(A1,ROW(INDIRECT("2:" & A1)))=0

which returns

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALS E;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRU E}

where this is true, it returns the number dividided by that array, false returns 0

IF(MOD(A1,ROW(INDIRECT("2:" & A1)))=0,A1/ROW(INDIRECT("2:" & A1)),0)

and you get

{0;9;0;0;0;0;0;3;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1}

Taking MAX gives you 9.

Note that you don't actually need the last ,0

=MAX(IF(MOD(A1,ROW(INDIRECT("2:" & A1)))=0,A1/ROW(INDIRECT("2:" & A1))))

[Andy Pope]

Indirect is a builtin function, so the help file should be able to enlight you. [Edit]Or read Bob's excellent explanation

Obviously 65536 is the maximum as a value greater than the number of rows will cause the ROW function to fail.

[mr.why]

THX.

just to clarify if i wanted the number itself then:

17 would return 17
20 would return 20

as i explained it the highest divisor (factor) of 17 (excluding itself) is 1

hope this explains it :)


Edit: just looked throught the explanation and I have marked it as solved as this is a VBA helpsite not an Excel one. But if you could answer the following.


can i clarify what is going on and that i understand it: What is in effect happening is a virtual list of numbers that are all being divided INTO my target number. It then returns the biggest answer (and as we start at 2 the number itself isn't there, but if we had started at 1 it would be)

[Bob Phillips]

THX.

just to clarify if i wanted the number itself then:

17 would return 17
20 would return 20

as i explained it the highest divisor (factor) of 17 (excluding itself) is 1

hope this explains it :)


Edit: just looked throught the explanation and I have marked it as solved as this is a VBA helpsite not an Excel one. But if you could answer the following.


can i clarify what is going on and that i understand it: What is in effect happening is a virtual list of numbers that are all being divided INTO my target number. It then returns the biggest answer (and as we start at 2 the number itself isn't there, but if we had started at 1 it would be)

My explanation covers it all.

If you started at 1, you would always get the number itself as the biggest divisor.