Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
I have a question on the funtion "MOD". When the number to be divided is more
than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
The things you learn on this forum.
Steve On Tue, 03 Oct 2006 13:41:02 +0100, J.H. wrote: I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error.. How can I fix this? Thanks. -- Steve (3) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
The number of digits should not be a problem. Excel supports up to 15
digits of precision and it will accept longer representations even if it truncates their precision to 15 digits. On the other hand, using a number literal as formatted (i.e. with the commas as thousand separator) WILL cause an error because commas are confused with the argument separator. Try entering the number in a different cell (say A1) and then using =MOD(A1,something). You should not get an error. HTH Kostis Vezerides J.H. wrote: I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
Kostis,
that's what I thought and tried it - MOD(123456789,2) returns 1 as expected, but MOD(1234567890,2) returns #NUM!, even if the number is in a different cell. I haven't come across this before. Pete vezerid wrote: The number of digits should not be a problem. Excel supports up to 15 digits of precision and it will accept longer representations even if it truncates their precision to 15 digits. On the other hand, using a number literal as formatted (i.e. with the commas as thousand separator) WILL cause an error because commas are confused with the argument separator. Try entering the number in a different cell (say A1) and then using =MOD(A1,something). You should not get an error. HTH Kostis Vezerides J.H. wrote: I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
Hmmmm,
now I understand the meaning of the second message in the thread. Well, this is news to me too! On the other hand, the following formula: =A1-FLOOR(A1,2) will return the same as MOD(A1,2) and it does not suffer from this problem, although it would be good only for positive numbers in A1. Microsoft suggest another identity, which I am sure will also work: MOD(n, d) = n - d*INT(n/d) HTH Kostis Pete_UK wrote: Kostis, that's what I thought and tried it - MOD(123456789,2) returns 1 as expected, but MOD(1234567890,2) returns #NUM!, even if the number is in a different cell. I haven't come across this before. Pete vezerid wrote: The number of digits should not be a problem. Excel supports up to 15 digits of precision and it will accept longer representations even if it truncates their precision to 15 digits. On the other hand, using a number literal as formatted (i.e. with the commas as thousand separator) WILL cause an error because commas are confused with the argument separator. Try entering the number in a different cell (say A1) and then using =MOD(A1,something). You should not get an error. HTH Kostis Vezerides J.H. wrote: I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
Perhaps this Microsoft Knowledge Base article explains:
http://support.microsoft.com/kb/119083/EN-US/ "If the divisor argument, multiplied by 134,217,728 (or 2 raised to the 27th power), is less than or equal to the number argument, the MOD() function returns a #NUM! error value. HTH, -- Kevin James. Tua'r Goleuni "J.H." wrote in message ... |I have a question on the funtion "MOD". When the number to be divided is more | than nine digits (like: 1,234,457,890), the funtion would return error. How | can I fix this? | | Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
Thank you for your method here.
Unfortunately, I have to deal with numbers more than 15 digits (such as 17 digits). Does it mean that I have no ways to manage such problems (if the number is more than 15 digits)? Thanks for your concern. €œvezerid€ç¼–写: Hmmmm, now I understand the meaning of the second message in the thread. Well, this is news to me too! On the other hand, the following formula: =A1-FLOOR(A1,2) will return the same as MOD(A1,2) and it does not suffer from this problem, although it would be good only for positive numbers in A1. Microsoft suggest another identity, which I am sure will also work: MOD(n, d) = n - d*INT(n/d) HTH Kostis Pete_UK wrote: Kostis, that's what I thought and tried it - MOD(123456789,2) returns 1 as expected, but MOD(1234567890,2) returns #NUM!, even if the number is in a different cell. I haven't come across this before. Pete vezerid wrote: The number of digits should not be a problem. Excel supports up to 15 digits of precision and it will accept longer representations even if it truncates their precision to 15 digits. On the other hand, using a number literal as formatted (i.e. with the commas as thousand separator) WILL cause an error because commas are confused with the argument separator. Try entering the number in a different cell (say A1) and then using =MOD(A1,something). You should not get an error. HTH Kostis Vezerides J.H. wrote: I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
Never mind MOD
Excel doesn't let you have more than 15 significant digits Steve On Tue, 03 Oct 2006 15:31:02 +0100, J.H. wrote: Thank you for your method here. Unfortunately, I have to deal with numbers more than 15 digits (such as 17 digits). Does it mean that I have no ways to manage such problems (if the number is more than 15 digits)? Thanks for your concern. €œvezerid€ç¼–写: Hmmmm, now I understand the meaning of the second message in the thread. Well, this is news to me too! On the other hand, the following formula: =A1-FLOOR(A1,2) will return the same as MOD(A1,2) and it does not suffer from this problem, although it would be good only for positive numbers in A1. Microsoft suggest another identity, which I am sure will also work: MOD(n, d) = n - d*INT(n/d) HTH Kostis Pete_UK wrote: Kostis, that's what I thought and tried it - MOD(123456789,2) returns 1 as expected, but MOD(1234567890,2) returns #NUM!, even if the number is in a different cell. I haven't come across this before. Pete vezerid wrote: The number of digits should not be a problem. Excel supports up to 15 digits of precision and it will accept longer representations even if it truncates their precision to 15 digits. On the other hand, using a number literal as formatted (i.e. with the commas as thousand separator) WILL cause an error because commas are confused with the argument separator. Try entering the number in a different cell (say A1) and then using =MOD(A1,something). You should not get an error. HTH Kostis Vezerides J.H. wrote: I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? Thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
Excel's documented limit for numeric display is 15 digits. This is based on
the limitations of the IEEE standard for double precision representation of numbers. Most computer software follows this standard, so if you need to do math with 17 digit numbers, you will need to be very particular in your choice of software. If you merely need to display 17 digits (e.g. account numbers), then make them text (prefix with an apostrophe) instead of numeric. Jerry "J.H." wrote: Thank you for your method here. Unfortunately, I have to deal with numbers more than 15 digits (such as 17 digits). Does it mean that I have no ways to manage such problems (if the number is more than 15 digits)? Thanks for your concern. €œvezerid€ç¼–写: Hmmmm, now I understand the meaning of the second message in the thread. Well, this is news to me too! On the other hand, the following formula: =A1-FLOOR(A1,2) will return the same as MOD(A1,2) and it does not suffer from this problem, although it would be good only for positive numbers in A1. Microsoft suggest another identity, which I am sure will also work: MOD(n, d) = n - d*INT(n/d) HTH Kostis Pete_UK wrote: Kostis, that's what I thought and tried it - MOD(123456789,2) returns 1 as expected, but MOD(1234567890,2) returns #NUM!, even if the number is in a different cell. I haven't come across this before. Pete vezerid wrote: The number of digits should not be a problem. Excel supports up to 15 digits of precision and it will accept longer representations even if it truncates their precision to 15 digits. On the other hand, using a number literal as formatted (i.e. with the commas as thousand separator) WILL cause an error because commas are confused with the argument separator. Try entering the number in a different cell (say A1) and then using =MOD(A1,something). You should not get an error. HTH Kostis Vezerides J.H. wrote: I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? Thanks. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
This was discussed at length 3 years ago
http://groups.google.com/group/micro...5701e983e926bc Jerry "J.H." wrote: I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? Thanks. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
"J.H." wrote in message
... I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? It's not a question of it being 9 digits. See http://support.microsoft.com/kb/119083/EN-US/ -- David Biddulph |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
I don't have enought disc space to keep that much backposts :)
Steve On Tue, 03 Oct 2006 15:57:02 +0100, Jerry W. Lewis wrote: This was discussed at length 3 years ago http://groups.google.com/group/micro...5701e983e926bc Jerry "J.H." wrote: I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? Thanks. -- Steve (3) |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
Steve,
You don't have to archive. Google does it for you. I use Google, newsgroups etc. to retrieve posts from years ago. I do it often. Info is still valuable. Epinn "SteveW" wrote in message news:op.tguppiujevjsnp@enigma03... I don't have enought disc space to keep that much backposts :) Steve On Tue, 03 Oct 2006 15:57:02 +0100, Jerry W. Lewis wrote: This was discussed at length 3 years ago http://groups.google.com/group/micro...5701e983e926bc Jerry "J.H." wrote: I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? Thanks. -- Steve (3) |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
:) You must read the :)
Seriously I don't post or read via google - newsreaders work well for me.. I agree it should be a prerequisite before 90% of the posters on here post 1. check archives 2. write the question in brief, but with all relevant information 3. read the question again before posting 4.... No. I'll stop there. I did try it out for *excel mod problem* and got some tech discussion on the 2^27 problem in the first 10 hits I have and would usually use these to get a bit more background on a problem Steve On Tue, 03 Oct 2006 17:27:47 +0100, Epinn wrote: Steve, You don't have to archive. Google does it for you. I use Google, newsgroups etc. to retrieve posts from years ago. I do it often. Info is still valuable. Epinn "SteveW" wrote in message news:op.tguppiujevjsnp@enigma03... I don't have enought disc space to keep that much backposts :) Steve On Tue, 03 Oct 2006 15:57:02 +0100, Jerry W. Lewis wrote: This was discussed at length 3 years ago http://groups.google.com/group/micro...5701e983e926bc Jerry "J.H." wrote: I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? Thanks. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
J.H. wrote...
I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? As others have pointed out, Excel's MOD function is flawed compared to what the hardware is capable of calculating. Instead of MOD(a,b) calls, use (a-b*int(a/b)). |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MOD
Unfortunately, I have to deal with numbers more than 15 digits (such as 17 digits). Does it mean that I have no ways to manage such problems (if the number is more than 15 digits)? Hi. Don't know what you have set up, so this is real general... Suppose you had the following text number with 21 digits in A1. '123456789012345678901 =MyMod(A1,7) returns the correct answer of 4. Function MyMod(n, m) MyMod = (CDec(n) - m * Int(CDec(n) / m)) End Function Again, very general, but may give you some ideas if you do in fact have numbers as text over 15 digits. HTH Dana DeLouis On Tue, 3 Oct 2006 07:31:02 -0700, J.H. wrote: Thank you for your method here. Unfortunately, I have to deal with numbers more than 15 digits (such as 17 digits). Does it mean that I have no ways to manage such problems (if the number is more than 15 digits)? Thanks for your concern. “vezerid”??? Hmmmm, now I understand the meaning of the second message in the thread. Well, this is news to me too! On the other hand, the following formula: =A1-FLOOR(A1,2) will return the same as MOD(A1,2) and it does not suffer from this problem, although it would be good only for positive numbers in A1. Microsoft suggest another identity, which I am sure will also work: MOD(n, d) = n - d*INT(n/d) HTH Kostis Pete_UK wrote: Kostis, that's what I thought and tried it - MOD(123456789,2) returns 1 as expected, but MOD(1234567890,2) returns #NUM!, even if the number is in a different cell. I haven't come across this before. Pete vezerid wrote: The number of digits should not be a problem. Excel supports up to 15 digits of precision and it will accept longer representations even if it truncates their precision to 15 digits. On the other hand, using a number literal as formatted (i.e. with the commas as thousand separator) WILL cause an error because commas are confused with the argument separator. Try entering the number in a different cell (say A1) and then using =MOD(A1,something). You should not get an error. HTH Kostis Vezerides J.H. wrote: I have a question on the funtion "MOD". When the number to be divided is more than nine digits (like: 1,234,457,890), the funtion would return error. How can I fix this? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|