Generate a divisor

[Ron]

Hello all,

I am creating a division worksheet for my daughter and I
am trying to figure out how to generate a divisor of an
even numerator that is randomly generated.

Thanks for your help,
Ron

 

[Dave Peterson]

I think I'd start the other way. Start with the divisor and multiply it by a
random integer to get the numerator.

 

[Bernie Deitrick]

Ron,

Though I would recommend choosing two factors and multiplying them to get
your original numerator, the sub below will ask for a number and return a
valid, randomly chosen divisor.

HTH,
Bernie
MS Excel MVP

Sub Test()
Dim myEven As Long
myEven = InputBox("Give me an even number")
MsgBox "A valid divisor of " & myEven & " is " & _
EvenDivisor(myEven) & "."
End Sub

Function EvenDivisor(EvenNum As Long) As Long
Dim i As Integer
Dim myRet As Variant
Dim myMsg As String
Dim myIndex As Integer

myRet = Factors(EvenNum)

EvenDivisor = 1
Randomize
For i = 1 To UBound(myRet)
myIndex = Int(Rnd() * (UBound(myRet) - LBound(myRet)) + 1.5)
EvenDivisor = EvenDivisor * myRet(myIndex)
myRet(myIndex) = 1
Next i
End Function

Function Factors(inVal As Long) As Variant
Dim myValue As Long
Dim myFactors() As Long
Dim myCount As Integer
Dim i As Long

myValue = inVal
myCount = 1
ReDim Preserve myFactors(1 To myCount)

While myValue Mod 2 = 0
ReDim Preserve myFactors(1 To myCount)
myFactors(myCount) = 2
myValue = myValue / 2
myCount = myCount + 1
Wend

OddFactors:

For i = 3 To myValue Step 2
If myValue Mod i = 0 Then
ReDim Preserve myFactors(1 To myCount)
myFactors(myCount) = i
myValue = myValue / i
myCount = myCount + 1
GoTo OddFactors:
End If
Next i

Factors = myFactors
End Function

 

[hrlngrv - ExcelForums.com]

Bernie Deitrick wrote..

Though I would recommend choosing two factors and multiplyin the
to get your original numerator, the sub below will ask for number an
return a valid, randomly chosen divisor

..

Ignorance of basic numeric programming is not bliss

Function Factors(inVal As Long) As Varian
Dim myValue As Lon
Dim myFactors() As Lon
Dim myCount As Intege
Dim i As Lon

myValue = inVa
myCount =
ReDim Preserve myFactors(1 To myCount

Why bother with a ReDim call here since it's the first statement i
both loops below

Why bother with ReDim calls at all inside the loops since any intege
N has *fewer* than 2*INT(SQRT(N)) distinct factors

While myValue Mod 2 =
ReDim Preserve myFactors(1 To myCount
myFactors(myCount) =
myValue = myValue /
myCount = myCount +
Wen

Looks like you didn't notice that you're modifying myValue in eac
loop..

OddFactors

For i = 3 To myValue Step
If myValue Mod i = 0 The
ReDim Preserve myFactors(1 To myCount
myFactors(myCount) =
myValue = myValue /
myCount = myCount +
GoTo OddFactors
End I
Next

...so that it's FUBAR when you enter this loop (it's always 1, so th
For loop never runs)

Even if you didn't fubar myValue, the GoTo would ensures that i wa
always 3. You didn't test the odd factors part, did you? How often d
you post untested code

Factors = myFactor
End Functio

The sensible way to do this i

Function factor(n As Long) As Varian
Dim i As Long, j As Long, k As Long, rv As Varian

If n < 0 The
k = 2 * CLng(Sqr(Abs(n))
ReDim rv(1 To k
rv(1) = -
rv(2) =
j =

ElseIf n > 0 The
k = 2 * CLng(Sqr(n)
ReDim rv(1 To k
rv(1) =
j =

Els
rv = Array(0
Exit Functio

End I

For i = 2 To Abs(n) \
If n Mod i = 0 The
j = j +
rv(j) =
End I
Next

If k > 2 The
j = j +
rv(j) =
End I

ReDim Preserve rv(1 To j
f = r

End Functio

 

[Bernie Deitrick]

message

Looks like you didn't notice that you're modifying myValue in each
loop...

myValue is a temporary variable, and changing its value is intentional. If
you ran the code, you would see why. Changing the value after each factor is
found and prior to iterating is a valid computational technique.

..so that it's FUBAR when you enter this loop (it's always 1, so the
For loop never runs).

Actually, the loop runs - searching for odd prime factors. Since 3 can be a
factor multiple times, it makes sense to start the search for odd prime
factors at 3 each time.

Even if you didn't fubar myValue, the GoTo would ensures that i was
always 3. You didn't test the odd factors part, did you? How often do
you post untested code?

Rarely, and only if the code is simple. How often do you make ignorant
comments on code that you don't bother to run? If you had bothered to run
the code in this case, you would have seen that the answers produced are
correct, and that the code works fine. But then, you wouldn't have
embarrassed yourself, so post away.

Bernie

 

[Harlan Grove]

Rarely, and only if the code is simple. How often do you make ignorant
comments on code that you don't bother to run?

Apparently too often.

Your code produces prime factorizations. So for 60, it returns {2,2,3,5}.
This isn't all the factors, which would be (ignoring 1 and 60)
{2,3,4,5,6,10,12,15,20,30}.

So my previous post was garbage except for the implied fact that running
ReDim Preserve inside both loops is poor coding.

 

[Tushar Mehta]

You may also want to look at a solution I put together some time ago to
teach arithmetic:
http://www.tushar-mehta.com/excel/education/

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Bernie Deitrick]

Your code produces prime factorizations. So for 60, it returns {2,2,3,5}.

This isn't all the factors, which would be (ignoring 1 and 60)
{2,3,4,5,6,10,12,15,20,30}.

Yes, but it's much faster to extract the prime factors and regenerate a
factor randomly by mutliplying a random set of the prime factors together -
especially since the OP only wanted one randomly selected factor. And that
is what my other function does.

I had copied the wrong prime factors function when I started - I had long
ago compared two different routines for extracting prime factors, and kept
both. Since no prime factor can be greater than the square root of the
original number, you can save a lot of time by doing the primes, especially
since you can extract the even prime factors (2) extremely quickly.

Bernie

 

[Harlan Grove]

Yes, but it's much faster to extract the prime factors and regenerate a
factor randomly by mutliplying a random set of the prime factors together -
especially since the OP only wanted one randomly selected factor. And that
is what my other function does.

A udf isn't necessary. Given an integer N as input, worksheet functions can
generate the factors. If Seq were defined as =ROW(INDIRECT("1:100")),

=SMALL(IF(MOD(N,Seq)=0,Seq),ROW(INDIRECT("1:"
&SUMPRODUCT(--(MOD(N,Seq)=0)))))

is the array of factors including 1 and N. A nontrivial (i.e., neither 1 nor
N) random factor could be drawn using the array formula

=SMALL(IF(MOD(N,Seq)=0,Seq),
INT(2+(SUMPRODUCT(--(MOD(N,Seq)=0))-2)*RAND()))

I'll guarantee you this last formula (possibly with Seq redefined to span a
larger sequence of integers) is much faster than any VBA udf you try to
write given the inherrent slowness of the Excel/VBA interface - even though
the array formula is brute force and redundant.

So speed isn't a reason to use a udf.

 

[Bernie Deitrick]

Harlan,

So speed isn't a reason to use a udf.

Speed is never a good reason to write a UDF, but flexibility is. Still,
that's a nifty formula/technique for integers less than 65537 - do you have
a similar one that will return the family of prime factors (with repeats),
or one that will work with larger integers (those that have factors greater
than 65536)?

Bernie

 

[Harlan Grove]

Speed is never a good reason to write a UDF, but flexibility is. Still,
that's a nifty formula/technique for integers less than 65537 - do you have
a similar one that will return the family of prime factors (with repeats),
or one that will work with larger integers (those that have factors greater
than 65536)?

One could be hacked, e.g., defining Seq as

ROW(INDIRECT("1:50000"))+50000*(COLUMN(INDIRECT("A:T"))-1)

However, one you're dealing with really big prime factors, you've likely
left the domain of integers exactly representable in 15 decimal digits.
There does come a point at which you should switch to a symbolic math
package.

As for the OP's problem, the array formula as given should be sufficient.