Is there really a number

D

Dsuperc

Board Regular
Joined
Apr 3, 2002
Messages
135
I was given a challenge by a friend to find a certain number.
This number when divided by 2 gives a full number and a remainder of 1
The same number when divided by 3 gives a full number and a remainder of 2
Again when divide by 4, remainder of 3
devided by 5, remainder 4
and so on until when divided by 10 gives a remainder of 9
Is there really that number?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Yep. It is 2519. Here is some VBA that figures it out (kind of crude maybe, but effective).
Code: 
Sub FindNumber()
    Dim x As Double
    Dim myCheck As Byte
 
    For x = 1 To 999999
        myCheck = 0
        If x Mod 2 = 1 Then myCheck = myCheck + 1
        If x Mod 3 = 2 Then myCheck = myCheck + 1
        If x Mod 4 = 3 Then myCheck = myCheck + 1
        If x Mod 5 = 4 Then myCheck = myCheck + 1
        If x Mod 6 = 5 Then myCheck = myCheck + 1
        If x Mod 7 = 6 Then myCheck = myCheck + 1
        If x Mod 8 = 7 Then myCheck = myCheck + 1
        If x Mod 9 = 8 Then myCheck = myCheck + 1
        If x Mod 10 = 9 Then myCheck = myCheck + 1
        If myCheck = 9 Then
            MsgBox "The number is " & x
            Exit For
        End If
    Next x
 
End Sub
 
Upvote 0
Here is a slight variation to my code that is 30 times faster (only uses 84 loops instead of 2519). Doesn't change the answer you get, only gets it faster.

I used the logic dividing by 10 leaves a remainder of 9 tells us that the number has to end in 9. So we only need to look at numbers ending in 9. If we look at the first three numbers ending in 9, and consider what the remainders are when we divide by 3:

9 MOD 3 = 0
19 MOD 3 = 1
29 MOD 3 = 2
and this pattern continues to repeat in this fashion.

Since we know that we need a remainder of 2 when divding by three, this tells us that we can start at 29, and step by 30 (check 29, 59, 89, etc). So here is my slightly modified, faster code:
Code: 
Sub FindNumber()
    Dim x As Double
    Dim myCheck As Byte
 
    For x = 29 To 999999 Step 30
        myCheck = 0
        If x Mod 2 = 1 Then myCheck = myCheck + 1
        If x Mod 3 = 2 Then myCheck = myCheck + 1
        If x Mod 4 = 3 Then myCheck = myCheck + 1
        If x Mod 5 = 4 Then myCheck = myCheck + 1
        If x Mod 6 = 5 Then myCheck = myCheck + 1
        If x Mod 7 = 6 Then myCheck = myCheck + 1
        If x Mod 8 = 7 Then myCheck = myCheck + 1
        If x Mod 9 = 8 Then myCheck = myCheck + 1
        If x Mod 10 = 9 Then myCheck = myCheck + 1
        If myCheck = 9 Then
            MsgBox "The number is " & x
            Exit For
        End If
    Next x
 
End Sub
Obviously, you can continue this logic further and make it even more efficient, but I just stuck with a few obvious details that don't entail too much insight.

Why bother if we already have the answer?
Because I am a computer/math geek and love this kind of stuff!
So, for my own amusement, really.:biggrin:
 
Upvote 0
Hi Folks,

There's lots of other numbers too. Consider:
Code: 
Sub FindNumbers()
Dim i As Integer
Dim x As Long
Dim myCheck As Long
Dim NumStr()
For x = 209 To 99999 Step 210
  myCheck = 0
  If x Mod 2 = 1 Then myCheck = myCheck + 1
  If x Mod 3 = 2 Then myCheck = myCheck + 1
  If x Mod 4 = 3 Then myCheck = myCheck + 1
  If x Mod 5 = 4 Then myCheck = myCheck + 1
  If x Mod 6 = 5 Then myCheck = myCheck + 1
  If x Mod 7 = 6 Then myCheck = myCheck + 1
  If x Mod 8 = 7 Then myCheck = myCheck + 1
  If x Mod 9 = 8 Then myCheck = myCheck + 1
  If x Mod 10 = 9 Then myCheck = myCheck + 1
  If myCheck = 9 Then
  i = i + 1
  ReDim Preserve NumStr(i)
  NumStr(i) = x
  End If
Next x
  MsgBox Join(NumStr, vbCrLf)
End Sub
Note that I've made the code yet more efficient by stepping in multiples of the primes from 1-9 (ie 1*2*3*5*7=210).

Cheers
 
Upvote 0
Joe and Paul, that's cool!

I slightly modified Paul's code to output the values to column 1. There are 395 such numbers between 1 and 999999...

Code: 
Sub FindNumbers()
Dim i As Integer
Dim x As Long
Dim myCheck As Long
Dim NumStr()
For x = 209 To 999999 Step 210
  myCheck = 0
  If x Mod 2 = 1 Then myCheck = myCheck + 1
  If x Mod 3 = 2 Then myCheck = myCheck + 1
  If x Mod 4 = 3 Then myCheck = myCheck + 1
  If x Mod 5 = 4 Then myCheck = myCheck + 1
  If x Mod 6 = 5 Then myCheck = myCheck + 1
  If x Mod 7 = 6 Then myCheck = myCheck + 1
  If x Mod 8 = 7 Then myCheck = myCheck + 1
  If x Mod 9 = 8 Then myCheck = myCheck + 1
  If x Mod 10 = 9 Then myCheck = myCheck + 1
  If myCheck = 9 Then
  i = i + 1
  ReDim Preserve NumStr(i)
  NumStr(i) = x
  End If
Next x
  Range("A1").Resize(i, 1) = WorksheetFunction.Transpose(NumStr)
End Sub

Denis
 
Upvote 0
Hi all,

I've thought a bit more about how to calculate the start point and interval for the problem. In reality, one can base this on the primes that comprise the numbers 1-9 (excluding 1). Because we're testing multiples of 2 and 3, we don't need to test 4, 6, 8 or 9 directly. Instead, we can restrict our testing to the highest multiple of each of these numbers < 10. Thus, the interval = 5 * 7 * 8 * 9 = 2520. The first value with the remainder of 9, is one less than that, ie 2519.

Also, instead of using 9 separate Mod tests, we can use a loop. My modifications to Denis' code becomes:
Code: 
Sub FindNumbers()
Dim i As Integer
Dim j As Integer
Dim x As Long
Dim myCheck As Long
Dim NumStr()
Dim Interval As Integer
' Calculate the Interval for all digits under 10, based on the minimum required # of primes
Interval = 2 * 2 * 2 * 3 * 3 * 5 * 7
' Decrement i to force the array to start at 0
i = -1
For x = Interval - 1 To 999999 Step Interval
  myCheck = 0
  For j = 2 To 10
    If x Mod j = j - 1 Then myCheck = myCheck + 1
  Next
  If myCheck = 9 Then
  i = i + 1
  ReDim Preserve NumStr(i)
  NumStr(i) = x
  End If
Next x
  Range("A1").Resize(i, 1) = WorksheetFunction.Transpose(NumStr)
End Sub
Cheers
 
Upvote 0
Paul, you gave me an idea. If you look at the pattern of the output numbers, they are all (2520*i)-1, where i is an integer. So here's some code to test out my contribution (taken pretty well directly from Joe), yours and a new pattern that just builds the array on the pattern.

Code: 
Sub Checkem()
    Dim i As Integer
    Dim StartTime As Single, _
        EndTime As Single
    Dim vTimes(40)
    
    For i = 1 To 40
        StartTime = Timer
        FindNumbers
        EndTime = Timer
        vTimes(i) = EndTime - StartTime
    Next i
    Cells(2, 4).Resize(40, 1) = WorksheetFunction.Transpose(vTimes)
    For i = 1 To 40
        StartTime = Timer
        FindNumbers_2
        EndTime = Timer
        vTimes(i) = EndTime - StartTime
    Next i
    Cells(2, 5).Resize(40, 1) = WorksheetFunction.Transpose(vTimes)
    For i = 1 To 40
        StartTime = Timer
        FindNumbers_3
        EndTime = Timer
        vTimes(i) = EndTime - StartTime
    Next i
    Cells(2, 6).Resize(40, 1) = WorksheetFunction.Transpose(vTimes)
    
End Sub
Sub FindNumbers()
Dim i As Integer
Dim x As Long
Dim myCheck As Long
Dim NumStr()
For x = 209 To 999999 Step 210
  myCheck = 0
  If x Mod 2 = 1 Then myCheck = myCheck + 1
  If x Mod 3 = 2 Then myCheck = myCheck + 1
  If x Mod 4 = 3 Then myCheck = myCheck + 1
  If x Mod 5 = 4 Then myCheck = myCheck + 1
  If x Mod 6 = 5 Then myCheck = myCheck + 1
  If x Mod 7 = 6 Then myCheck = myCheck + 1
  If x Mod 8 = 7 Then myCheck = myCheck + 1
  If x Mod 9 = 8 Then myCheck = myCheck + 1
  If x Mod 10 = 9 Then myCheck = myCheck + 1
  If myCheck = 9 Then
  i = i + 1
  ReDim Preserve NumStr(i)
  NumStr(i) = x
  End If
Next x
  Range("A1").Resize(i, 1) = WorksheetFunction.Transpose(NumStr)
End Sub

Sub FindNumbers_2()
Dim i As Integer
Dim j As Integer
Dim x As Long
Dim myCheck As Long
Dim NumStr()
Dim Interval As Integer
' Calculate the Interval for all digits under 10, based on the minimum required # of primes
Interval = 2 * 2 * 2 * 3 * 3 * 5 * 7
' Decrement i to force the array to start at 0
i = -1
For x = Interval - 1 To 999999 Step Interval
  myCheck = 0
  For j = 2 To 10
    If x Mod j = j - 1 Then myCheck = myCheck + 1
  Next
  If myCheck = 9 Then
  i = i + 1
  ReDim Preserve NumStr(i)
  NumStr(i) = x
  End If
Next x
  Range("B1").Resize(i, 1) = WorksheetFunction.Transpose(NumStr)
End Sub

Sub FindNumbers_3()
    Dim i As Long
    Dim j As Integer
    Dim Interval As Integer
    Dim NumStr()
    
    ' Calculate the Interval for all digits under 10, based on the minimum required # of primes
    Interval = 2 * 2 * 2 * 3 * 3 * 5 * 7
    j = Int(999999 / Interval)
    
    ReDim NumStr(j)
    For i = 1 To j
        NumStr(i) = (Interval * i) - 1
    Next i
    Range("B1").Resize(i, 1) = WorksheetFunction.Transpose(NumStr)
End Sub
Timing the three (sum of 40 runs each) gave this result on my machine:

#1 0.242188
#2 0.171875
#3 0.164063


Denis
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Combining/nesting formulas
Replies
8
Views
158
Alex Blakenburg
A
dreid1011
  • Solved
MS Word Mail Merge: Group or Link fields across multiple pages/sections?
2
Replies
13
Views
182
dreid1011
dreid1011
J
  • Question Question
Google Sheets formula for randomly choosing an option
Replies
0
Views
154
jwoww
J
K
  • Question Question
Pull a column from a different sheet on or after a certain date and convert that into a number by counting a certain word
Replies
0
Views
289
khollibygolli
K
L
  • Question Question
Bar Graph not showing Correctly
Replies
0
Views
246
LllopezXC
L

Forum statistics

Threads
1,225,382
Messages
6,184,640
Members
453,248
Latest member
levi_15

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top