# Truncate/Mod

G

#### Guest

I've been working on this for the good portion of today and Im very baffled.
I'm probably overlooking something very simple. Anyway I need to get the mod
of some very very large numbers. So large I need to split it down into
smaller componants just to load it into a double (Im doing this all for fun.
I just really want to figure this out because there must be a way). Anyway,
so I've split the number into a much smaller number 1.9923E+130
approximately. I need to mod this number by a variable that can reach well
into the 60,000s. Unfortunately as far as I can tell the Mod operator is
only capable of returning an integer. I believe integers can only go up to
16k or 32k.

Right now building and testing the program Im modding this 1.9923E+130 by
17947. I keep getting a variable overflow message. I've attempted to write
my own Mod function which needed its own Truncate function (Does VBA even
that the ability to truncate?!). It works find on smaller numbers.
Something well into the quintillians I believe. But when it faces by
1.9923E+130 my truncate function returns a 1. Okay, well my truncate
function is probably doing it all wrong. Its basically:

Arr = Split(number, "."
trunc = Arr(0)

My Mod function is something along the lines of:

number = number / divisor
TruncNum = trunc(number)
fMod = (number - TruncNum) * divisor

So if anyone has any suggestions or sollutions please let me know. If
pursuing this little project of mine is just rediculous please let me know
too. I've been having fun thusfar minus the arteries which may have ruptured

B

#### Bob Phillips

Help says for Mod

The modulus, or remainder, operator divides number1 by number2 (rounding
floating-point numbers to integers)

so it indeed only works on integers.

Integers can do 32k, but Longs will cover -2,147,483,648 to 2,147,483,647.

INT is the equivalent of TRUNC - INT(7.6) is 7

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

J

#### Jake Marx

Hi Abode,
I've been working on this for the good portion of today and Im very
baffled. I'm probably overlooking something very simple. Anyway I
need to get the mod of some very very large numbers. So large I need
to split it down into smaller componants just to load it into a
double (Im doing this all for fun. I just really want to figure this
out because there must be a way). Anyway, so I've split the number
into a much smaller number 1.9923E+130 approximately. I need to mod
this number by a variable that can reach well into the 60,000s.
Unfortunately as far as I can tell the Mod operator is only capable
of returning an integer. I believe integers can only go up to 16k or
32k.

As Bob said in his post - the Mod operator will not work on very large
numbers.
Right now building and testing the program Im modding this
1.9923E+130 by 17947. I keep getting a variable overflow message.
I've attempted to write my own Mod function which needed its own
Truncate function (Does VBA even that the ability to truncate?!).

You can use Worksheet functions in VBA - the FLOOR Worksheet function will
truncate numbers of type Double:

?Application.Floor(2983482374222.23, 1)
2983482374222

You could probably use this as part of your own Mod function. You'll have
to use variables of type Double everywhere to avoid overflows. But then
you're dealing with floating-point precision, and you're likely to get
inaccurate results with numbers that large. I wrote my own Mod and was
getting negative results here and there, which I'm attributing to this issue
(although I could have made a mistake <g>). Anywhere you need to truncate a
It
works find on smaller numbers. Something well into the quintillians I
believe. But when it faces by
1.9923E+130 my truncate function returns a 1. Okay, well my truncate
function is probably doing it all wrong. Its basically:

Arr = Split(number, "."
trunc = Arr(0)

That should work.
My Mod function is something along the lines of:

number = number / divisor
TruncNum = trunc(number)
fMod = (number - TruncNum) * divisor

This is where I think the floating point issues would come in, which is
probably why you're seeing inaccurate results.
So if anyone has any suggestions or sollutions please let me know. If
pursuing this little project of mine is just rediculous please let me
know too. I've been having fun thusfar minus the arteries which may
have ruptured in my head. Blah.

I don't think your project is ridiculous, but you're probably going to run
into a lot of limitations using Excel for this. I would suggest Matlab or
some other tool suited for this type of thing. Just my \$.02....

--
Regards,

Jake Marx

G

#### Guest

Excel uses IEEE standard double precision, which is only capable of reliably
representing the first 15 digits of a number. For numbers in the range of
1.9923E+130, that means that anything bellow 1E+115 is essentially random
noise.

If you are trying to study the internal representation of numbers, then you
would probably get more use from

Otherwise you are probably trying to do something that is well beyond the
capabilities of almost all computer software other than symbolic manipulators
such as Maple, Mathematica, MacSyma and open source packages like Maxima
http://maxima.sourceforge.net/

You might also be able to get somewhere with Excel add-ins that support user
specified numeric precision, like
http://digilander.libero.it/foxes/index.htm
http://precisioncalc.com/

Jerry

D

#### Dana DeLouis

Right now building and testing the program I am modeling this...
1.9923E+130 by 17947.
I keep getting a variable overflow message.

Hi. If you are trying to do large Mod's with Excel, here is a very general
technique mentioned by Harlan Grove using vba.
First off, Excel's vba's MOD function will probably not work. However, we
can use your Mod function as a workaround.

Reference:
XL: MOD() Function Returns #NUM! Error Value
http://support.microsoft.com/default.aspx?scid=kb;en-us;119083

It's probably easier to use a math program for this demo, but it should be
easy to follow.
Let's scale the problem down to fit here. You should be able to adjust it

Our two numbers...
n = 199230000000000000000000000000;
m = 17947;

We are trying to use Excel to find this...

Mod[n, m]
13321

So for now, we know the answer to be 13321.
= = = = = = = = = = = = = = = = = = = = = = = =

First, break the large number down into manageable sizes. We'll use a size
of 6. i.e. Place each group of 6 numbers into an array.
199230-000000-000000-000000-000000

To add 6 zero's to a number, multiply by 1000000.
Scale = 1000000;

Our first manageable Mod operation:
Mod[199230, m]
1813

Now loop 4 times.
Take previous answer, and append the next 6 numbers.

Mod[1813*Scale, m]
12007

Mod[12007*Scale, m]
8325

Mod[8325*Scale, m]
14845

Our last loop should give us our answer.
Mod[14845*Scale, m]
13321

Which it does. :>)

G

#### Guest

Thanks to everyone for the very helpful replies. I'm probably just going to
drop this or scale it WAY down. I figure Im going to need pinpoint accuracy
to meet the goal of my project which I wont be able to do with Excel. Anyway
much of this information will help me in future programs though so thank you
very much for the help.

Dana DeLouis said:
Right now building and testing the program I am modeling this...
1.9923E+130 by 17947.
I keep getting a variable overflow message.

Hi. If you are trying to do large Mod's with Excel, here is a very general
technique mentioned by Harlan Grove using vba.
First off, Excel's vba's MOD function will probably not work. However, we
can use your Mod function as a workaround.

Reference:
XL: MOD() Function Returns #NUM! Error Value
http://support.microsoft.com/default.aspx?scid=kb;en-us;119083

It's probably easier to use a math program for this demo, but it should be
easy to follow.
Let's scale the problem down to fit here. You should be able to adjust it

Our two numbers...
n = 199230000000000000000000000000;
m = 17947;

We are trying to use Excel to find this...

Mod[n, m]
13321

So for now, we know the answer to be 13321.
= = = = = = = = = = = = = = = = = = = = = = = =

First, break the large number down into manageable sizes. We'll use a size
of 6. i.e. Place each group of 6 numbers into an array.
199230-000000-000000-000000-000000

To add 6 zero's to a number, multiply by 1000000.
Scale = 1000000;

Our first manageable Mod operation:
Mod[199230, m]
1813

Now loop 4 times.
Take previous answer, and append the next 6 numbers.

Mod[1813*Scale, m]
12007

Mod[12007*Scale, m]
8325

Mod[8325*Scale, m]
14845

Our last loop should give us our answer.
Mod[14845*Scale, m]
13321

Which it does. :>)
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003

Abode said:
I've been working on this for the good portion of today and Im very
baffled.
I'm probably overlooking something very simple. Anyway I need to get the
mod
of some very very large numbers. So large I need to split it down into
smaller componants just to load it into a double (Im doing this all for
fun.
I just really want to figure this out because there must be a way).
Anyway,
so I've split the number into a much smaller number 1.9923E+130
approximately. I need to mod this number by a variable that can reach
well
into the 60,000s. Unfortunately as far as I can tell the Mod operator is
only capable of returning an integer. I believe integers can only go up
to
16k or 32k.

Right now building and testing the program Im modding this 1.9923E+130 by
17947. I keep getting a variable overflow message. I've attempted to
write
my own Mod function which needed its own Truncate function (Does VBA even
that the ability to truncate?!). It works find on smaller numbers.
Something well into the quintillians I believe. But when it faces by
1.9923E+130 my truncate function returns a 1. Okay, well my truncate
function is probably doing it all wrong. Its basically:

Arr = Split(number, "."
trunc = Arr(0)

My Mod function is something along the lines of:

number = number / divisor
TruncNum = trunc(number)
fMod = (number - TruncNum) * divisor

So if anyone has any suggestions or sollutions please let me know. If
pursuing this little project of mine is just rediculous please let me know
too. I've been having fun thusfar minus the arteries which may have
ruptured

D

#### Dana DeLouis

I figure I'm going to need pinpoint accuracy
to meet the goal of my project which I wont be able to do with Excel.

There may be workarounds for what you are trying to do. For example, see if
you can represent your large number as a string.
For example, if you represent your large number as the string "19923"
followed by 126 "0"'s, then perhaps the following:
We note that the answer to 1.9923 *10^130 mod 17947 equals 4749 if we
"assume" your large number is a whole number. This answer comes from
another program. Let's see if we can get the same answer using Excel.
The following "Test" routine returns 4749 also. :>)

Sub Test()
Dim n, m
n = "19923" & WorksheetFunction.Rept("0", 126)
m = 17947
Debug.Print MyBigMod(n, m)
End Sub
'= = = = = = = = = = = = = = = = =

Function MyBigMod(ByVal n, m)
Dim x

x = MyMod(Left(n, 6), m)
n = Mid(n, 7)

Do While Len(n) > 0
x = MyMod(x & Left(n, 6), m)
n = Mid(n, 7)
Loop
MyBigMod = x
End Function

Private Function MyMod(x, y)
MyMod = x - Int(x / y) * y
End Function

There are workarounds if the second number was much larger also.
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003

Abode said:
Thanks to everyone for the very helpful replies. I'm probably just going
to
drop this or scale it WAY down. I figure Im going to need pinpoint
accuracy
to meet the goal of my project which I wont be able to do with Excel.
Anyway
much of this information will help me in future programs though so thank
you
very much for the help.

Dana DeLouis said:
Right now building and testing the program I am modeling this...
1.9923E+130 by 17947.
I keep getting a variable overflow message.

Hi. If you are trying to do large Mod's with Excel, here is a very
general
technique mentioned by Harlan Grove using vba.
First off, Excel's vba's MOD function will probably not work. However,
we
can use your Mod function as a workaround.

Reference:
XL: MOD() Function Returns #NUM! Error Value
http://support.microsoft.com/default.aspx?scid=kb;en-us;119083

It's probably easier to use a math program for this demo, but it should
be
easy to follow.
Let's scale the problem down to fit here. You should be able to adjust
it

Our two numbers...
n = 199230000000000000000000000000;
m = 17947;

We are trying to use Excel to find this...

Mod[n, m]
13321

So for now, we know the answer to be 13321.
= = = = = = = = = = = = = = = = = = = = = = = =

First, break the large number down into manageable sizes. We'll use a
size
of 6. i.e. Place each group of 6 numbers into an array.
199230-000000-000000-000000-000000

To add 6 zero's to a number, multiply by 1000000.
Scale = 1000000;

Our first manageable Mod operation:
Mod[199230, m]
1813

Now loop 4 times.
Take previous answer, and append the next 6 numbers.

Mod[1813*Scale, m]
12007

Mod[12007*Scale, m]
8325

Mod[8325*Scale, m]
14845

Our last loop should give us our answer.
Mod[14845*Scale, m]
13321

Which it does. :>)
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003

Abode said:
I've been working on this for the good portion of today and Im very
baffled.
I'm probably overlooking something very simple. Anyway I need to get
the
mod
of some very very large numbers. So large I need to split it down into
smaller componants just to load it into a double (Im doing this all for
fun.
I just really want to figure this out because there must be a way).
Anyway,
so I've split the number into a much smaller number 1.9923E+130
approximately. I need to mod this number by a variable that can reach
well
into the 60,000s. Unfortunately as far as I can tell the Mod operator
is
only capable of returning an integer. I believe integers can only go
up
to
16k or 32k.

Right now building and testing the program Im modding this 1.9923E+130
by
17947. I keep getting a variable overflow message. I've attempted to
write
my own Mod function which needed its own Truncate function (Does VBA
even
that the ability to truncate?!). It works find on smaller numbers.
Something well into the quintillians I believe. But when it faces by
1.9923E+130 my truncate function returns a 1. Okay, well my truncate
function is probably doing it all wrong. Its basically:

Arr = Split(number, "."
trunc = Arr(0)

My Mod function is something along the lines of:

number = number / divisor
TruncNum = trunc(number)
fMod = (number - TruncNum) * divisor

So if anyone has any suggestions or sollutions please let me know. If
pursuing this little project of mine is just rediculous please let me
know
too. I've been having fun thusfar minus the arteries which may have
ruptured

G

#### Guest

Im sure there is a way to get the program to work but its above my level of
programming and it would take far too long for the computer to calculate.
Thus far I've tried working around the computers limitations already and my
insanely large number of 1.9923 * 10^130 is broken down from what I really
want to calculate (1978^11787) which a double cant store. I may get around
to trying again later or working with smaller scales but for now its a rather
low priority for me. I tried to create it to easy my mind from work which it
very much had the opposite effect.

Dana DeLouis said:
I figure I'm going to need pinpoint accuracy
to meet the goal of my project which I wont be able to do with Excel.

There may be workarounds for what you are trying to do. For example, see if
you can represent your large number as a string.
For example, if you represent your large number as the string "19923"
followed by 126 "0"'s, then perhaps the following:
We note that the answer to 1.9923 *10^130 mod 17947 equals 4749 if we
"assume" your large number is a whole number. This answer comes from
another program. Let's see if we can get the same answer using Excel.
The following "Test" routine returns 4749 also. :>)

Sub Test()
Dim n, m
n = "19923" & WorksheetFunction.Rept("0", 126)
m = 17947
Debug.Print MyBigMod(n, m)
End Sub
'= = = = = = = = = = = = = = = = =

Function MyBigMod(ByVal n, m)
Dim x

x = MyMod(Left(n, 6), m)
n = Mid(n, 7)

Do While Len(n) > 0
x = MyMod(x & Left(n, 6), m)
n = Mid(n, 7)
Loop
MyBigMod = x
End Function

Private Function MyMod(x, y)
MyMod = x - Int(x / y) * y
End Function

There are workarounds if the second number was much larger also.
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003

Abode said:
Thanks to everyone for the very helpful replies. I'm probably just going
to
drop this or scale it WAY down. I figure Im going to need pinpoint
accuracy
to meet the goal of my project which I wont be able to do with Excel.
Anyway
much of this information will help me in future programs though so thank
you
very much for the help.

Dana DeLouis said:
Right now building and testing the program I am modeling this...
1.9923E+130 by 17947.
I keep getting a variable overflow message.

Hi. If you are trying to do large Mod's with Excel, here is a very
general
technique mentioned by Harlan Grove using vba.
First off, Excel's vba's MOD function will probably not work. However,
we
can use your Mod function as a workaround.

Reference:
XL: MOD() Function Returns #NUM! Error Value
http://support.microsoft.com/default.aspx?scid=kb;en-us;119083

It's probably easier to use a math program for this demo, but it should
be
easy to follow.
Let's scale the problem down to fit here. You should be able to adjust
it

Our two numbers...
n = 199230000000000000000000000000;
m = 17947;

We are trying to use Excel to find this...

Mod[n, m]
13321

So for now, we know the answer to be 13321.
= = = = = = = = = = = = = = = = = = = = = = = =

First, break the large number down into manageable sizes. We'll use a
size
of 6. i.e. Place each group of 6 numbers into an array.
199230-000000-000000-000000-000000

To add 6 zero's to a number, multiply by 1000000.
Scale = 1000000;

Our first manageable Mod operation:
Mod[199230, m]
1813

Now loop 4 times.
Take previous answer, and append the next 6 numbers.

Mod[1813*Scale, m]
12007

Mod[12007*Scale, m]
8325

Mod[8325*Scale, m]
14845

Our last loop should give us our answer.
Mod[14845*Scale, m]
13321

Which it does. :>)
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003

I've been working on this for the good portion of today and Im very
baffled.
I'm probably overlooking something very simple. Anyway I need to get
the
mod
of some very very large numbers. So large I need to split it down into
smaller componants just to load it into a double (Im doing this all for
fun.
I just really want to figure this out because there must be a way).
Anyway,
so I've split the number into a much smaller number 1.9923E+130
approximately. I need to mod this number by a variable that can reach
well
into the 60,000s. Unfortunately as far as I can tell the Mod operator
is
only capable of returning an integer. I believe integers can only go
up
to
16k or 32k.

Right now building and testing the program Im modding this 1.9923E+130
by
17947. I keep getting a variable overflow message. I've attempted to
write
my own Mod function which needed its own Truncate function (Does VBA
even
that the ability to truncate?!). It works find on smaller numbers.
Something well into the quintillians I believe. But when it faces by
1.9923E+130 my truncate function returns a 1. Okay, well my truncate
function is probably doing it all wrong. Its basically:

Arr = Split(number, "."
trunc = Arr(0)

My Mod function is something along the lines of:

number = number / divisor
TruncNum = trunc(number)
fMod = (number - TruncNum) * divisor

So if anyone has any suggestions or sollutions please let me know. If
pursuing this little project of mine is just rediculous please let me
know
too. I've been having fun thusfar minus the arteries which may have
ruptured

D

#### Dana DeLouis

...it would take far too long for the computer to calculate.
...I really want to calculate (1978^11787).

Oh. That's different. :>)
I know you don't want to program this, but as a side note, there are
workarounds with Excel for even these types of calculations.

Your number has 38,853 digits in it. Excel has a Fourier Program in the
analysis tookpak that can, with very small programming, handle about
Fourier Program to bump it up to your size problem.
So...we could do it "kind of quickly", but it sounds like you need a
dedicated math program. :>) Good luck.
--
Dana DeLouis
Windows XP, Office 2003

Abode said:
Im sure there is a way to get the program to work but its above my level
of
programming and it would take far too long for the computer to calculate.
Thus far I've tried working around the computers limitations already and
my
insanely large number of 1.9923 * 10^130 is broken down from what I really
want to calculate (1978^11787) which a double cant store. I may get
around
to trying again later or working with smaller scales but for now its a
rather
low priority for me. I tried to create it to easy my mind from work which
it
very much had the opposite effect.
<snip>

D

#### Dana DeLouis

want to calculate (1978^11787)
...I need to mod this number by a variable (~ 60,000)

I thought I would just mention this. If you are actually trying to
calculate:

=MOD(1978 ^ 11787, 17947)

If this is the "actual" problem, then Excel vba "can" do this quickly.

In Math, one would never calculate the 38,853 digit number first. There are
much more efficient algorithms for these
types of problem.
The correct algorithm is called "PowerMod."
I know you don't want a program, but if you change your mind, there are some
interesting codes if you do a Google search.
I've picked up an excellent programming tip from some of these.

So, if this is the actual problem, then yes, Excel vba can do this quickly.
Good luck.

=PowerMod(1978, 11787, 17947)
11854

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003

<snip>