Generate a divisor
Ron
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
random integer to get the numerator.
--
Dave Peterson
ec3...@msn.com
@consumerdotorg Bernie Deitrick
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
"Ron" <anon...@discussions.microsoft.com> wrote in message
news:00cb01c49758$b2f93810$a301...@phx.gbl...
hrlngrv - ExcelForums.com
>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.
Ignorance of basic numeric programming is not bliss.
>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)
Why bother with a ReDim call here since it's the first statement in
both loops below?
Why bother with ReDim calls at all inside the loops since any integer
N has *fewer* than 2*INT(SQRT(N)) distinct factors?
>While myValue Mod 2 = 0
> ReDim Preserve myFactors(1 To myCount)
> myFactors(myCount) = 2
> myValue = myValue / 2
> myCount = myCount + 1
>Wend
Looks like you didn't notice that you're modifying myValue in each
loop...
>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
...so that it's FUBAR when you enter this loop (it's always 1, so the
For loop never runs).
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?
>Factors = myFactors
>End Function
The sensible way to do this is
Function factor(n As Long) As Variant
Dim i As Long, j As Long, k As Long, rv As Variant
If n < 0 Then
k = 2 * CLng(Sqr(Abs(n)))
ReDim rv(1 To k)
rv(1) = -1
rv(2) = 1
j = 2
ElseIf n > 0 Then
k = 2 * CLng(Sqr(n))
ReDim rv(1 To k)
rv(1) = 1
j = 1
Else
rv = Array(0)
Exit Function
End If
For i = 2 To Abs(n) \ 2
If n Mod i = 0 Then
j = j + 1
rv(j) = i
End If
Next i
If k > 2 Then
j = j + 1
rv(j) = n
End If
ReDim Preserve rv(1 To j)
f = rv
End Function
---------
Message sent via www.excelforums.com
@consumerdotorg Bernie Deitrick
message
<snip>
> 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.
<further snip>
> ..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
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
In article <00cb01c49758$b2f93810$a301...@phx.gbl>,
anon...@discussions.microsoft.com says...
@consumerdotorg Bernie Deitrick
> 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
>>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.
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.
@consumerdotorg Bernie Deitrick
> 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" <hrl...@aol.com> wrote
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.