Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A rounding oddity
To help answer a question on this group, I wanted to convert the decimal
part of a number (in the set 1, 1.1, 1.2, ...... 9.7, 9.8, 9.9 ) to a letter. Thus 2.1 would yield a, 2.2 would give b. I experimented with =CHOOSE(MOD(A1,1)*10+1,"z","a","b","c","d","e","f" ,"g","h","i") but it did not quite work (for example 3.8 gave 'g' and not 'h') So I tried =CHOOSE(MOD(A1*10,10)+1,"z","a","b","c","d","e","f ","g","h","i") and this worked. Odd since, at the integer level, =MOD(A1,1)*10+1 and =MOD(A1*10,10)+1 give the same result. Using the Evaluate Formula tool (I work with XL 2003) I was unable to see why my original formula did not work. =CHOOSE(MOD(3.8,1)*10+1,"z","a","b","c","d","e","f ","g","h","i") yields =CHOOSE(9,"z","a","b","c","d","e","f","g","h","i") but the next result is g not h Very odd!!! Happy New Year to all -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A rounding oddity
Bernard,
Starting at 3.1, and incrementing by .1, I get a b b c e f g h Anyway, instead of =CHOOSE(MOD(3.8,1)*10+1,"z","a","b","c","d","e","f ","g","h","i") try =CHOOSE(ROUND(MOD(3.8,1)*10+1,0),"z","a","b","c"," d","e","f","g","h","i") I'm sure MOD is affected by binary math, and .... HTH, Bernie MS Excel MVP "Bernard Liengme" wrote in message ... To help answer a question on this group, I wanted to convert the decimal part of a number (in the set 1, 1.1, 1.2, ...... 9.7, 9.8, 9.9 ) to a letter. Thus 2.1 would yield a, 2.2 would give b. I experimented with =CHOOSE(MOD(A1,1)*10+1,"z","a","b","c","d","e","f" ,"g","h","i") but it did not quite work (for example 3.8 gave 'g' and not 'h') So I tried =CHOOSE(MOD(A1*10,10)+1,"z","a","b","c","d","e","f ","g","h","i") and this worked. Odd since, at the integer level, =MOD(A1,1)*10+1 and =MOD(A1*10,10)+1 give the same result. Using the Evaluate Formula tool (I work with XL 2003) I was unable to see why my original formula did not work. =CHOOSE(MOD(3.8,1)*10+1,"z","a","b","c","d","e","f ","g","h","i") yields =CHOOSE(9,"z","a","b","c","d","e","f","g","h","i") but the next result is g not h Very odd!!! Happy New Year to all -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A rounding oddity
Bernard Liengme wrote:
To help answer a question on this group, I wanted to convert the decimal part of a number (in the set 1, 1.1, 1.2, ...... 9.7, 9.8, 9.9 ) to a letter. Thus 2.1 would yield a, 2.2 would give b. I experimented with =CHOOSE(MOD(A1,1)*10+1,"z","a","b","c","d","e","f" ,"g","h","i") but it did not quite work (for example 3.8 gave 'g' and not 'h') So I tried =CHOOSE(MOD(A1*10,10)+1,"z","a","b","c","d","e","f ","g","h","i") and this worked. Odd since, at the integer level, =MOD(A1,1)*10+1 and =MOD(A1*10,10)+1 give the same result. Using the Evaluate Formula tool (I work with XL 2003) I was unable to see why my original formula did not work. =CHOOSE(MOD(3.8,1)*10+1,"z","a","b","c","d","e","f ","g","h","i") yields =CHOOSE(9,"z","a","b","c","d","e","f","g","h","i") but the next result is g not h Very odd!!! Happy New Year to all Hi Bernard, interesting, this works fine =INDEX({"z";"a";"b";"c";"d";"e";"f";"g";"h";"i"},M OD(A1,1)*10+1) and is non volatile this works as well =CHOOSE(INT(MOD(A1,1)*10)+1,"z","a","b","c","d","e ","f","g","h","i") another option might be =CHAR(MOD(A1,1)*10+96) but it needs editing depending on what the result should be if A1 is an integer or is blank Regards, Peo Sjoblom |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A rounding oddity
3.8 has no exact finite precision binary representation, and the best
approximation is less than the exact value, so the value of =MOD(3.8,1)*10+1 is 9 -2^-49, instead of 9. As a result, CHOOSE truncates it to 8, as documented. Jerry "Bernard Liengme" wrote: To help answer a question on this group, I wanted to convert the decimal part of a number (in the set 1, 1.1, 1.2, ...... 9.7, 9.8, 9.9 ) to a letter. Thus 2.1 would yield a, 2.2 would give b. I experimented with =CHOOSE(MOD(A1,1)*10+1,"z","a","b","c","d","e","f" ,"g","h","i") but it did not quite work (for example 3.8 gave 'g' and not 'h') So I tried =CHOOSE(MOD(A1*10,10)+1,"z","a","b","c","d","e","f ","g","h","i") and this worked. Odd since, at the integer level, =MOD(A1,1)*10+1 and =MOD(A1*10,10)+1 give the same result. Using the Evaluate Formula tool (I work with XL 2003) I was unable to see why my original formula did not work. =CHOOSE(MOD(3.8,1)*10+1,"z","a","b","c","d","e","f ","g","h","i") yields =CHOOSE(9,"z","a","b","c","d","e","f","g","h","i") but the next result is g not h Very odd!!! Happy New Year to all -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A rounding oddity
Hi Bernard
Yes, I get the same result as you with XL2003. With the series 2.0,2.1 etc through to 3.0 in A1:A11 I get the letter series z,a,b,b,c,e,f,g,g,i,z whereas with your second formula I get the correct series z,a,b,c,d,e,f,g,h,i,z Looking at the =MOD(A1,1)*10+1 part of the formula and the =MOD(A1*10,10)+1without the Choose function the return is the series of numbers 1,2,3,4,5,6,7,8,9,10,1 in both cases, with no detectable difference even when extending decimal places to 30. It therefore appears as though Choose is giving rise to the error. Amending the first formula to =CHOOSE(ROUND(MOD(A1,1)*10+1,0),"z","a","b","c","d ","e","f","g","h","i") gives the correct results. As you say, very odd indeed. I would also like to send best wishes to you (and everyone else) for the New Year. -- Regards Roger Govier "Bernard Liengme" wrote in message ... To help answer a question on this group, I wanted to convert the decimal part of a number (in the set 1, 1.1, 1.2, ...... 9.7, 9.8, 9.9 ) to a letter. Thus 2.1 would yield a, 2.2 would give b. I experimented with =CHOOSE(MOD(A1,1)*10+1,"z","a","b","c","d","e","f" ,"g","h","i") but it did not quite work (for example 3.8 gave 'g' and not 'h') So I tried =CHOOSE(MOD(A1*10,10)+1,"z","a","b","c","d","e","f ","g","h","i") and this worked. Odd since, at the integer level, =MOD(A1,1)*10+1 and =MOD(A1*10,10)+1 give the same result. Using the Evaluate Formula tool (I work with XL 2003) I was unable to see why my original formula did not work. =CHOOSE(MOD(3.8,1)*10+1,"z","a","b","c","d","e","f ","g","h","i") yields =CHOOSE(9,"z","a","b","c","d","e","f","g","h","i") but the next result is g not h Very odd!!! Happy New Year to all -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A rounding oddity
Roger
Using this method: =CHOOSE(ROUND(MOD(A1,1)*10+1,0),"z","a","b","c","d ","e","f","g","h","i") how do you get to replace the decimal without losing the whole number (ie. 5.1 becomes 5a) "Roger Govier" wrote: Hi Bernard Yes, I get the same result as you with XL2003. With the series 2.0,2.1 etc through to 3.0 in A1:A11 I get the letter series z,a,b,b,c,e,f,g,g,i,z whereas with your second formula I get the correct series z,a,b,c,d,e,f,g,h,i,z Looking at the =MOD(A1,1)*10+1 part of the formula and the =MOD(A1*10,10)+1without the Choose function the return is the series of numbers 1,2,3,4,5,6,7,8,9,10,1 in both cases, with no detectable difference even when extending decimal places to 30. It therefore appears as though Choose is giving rise to the error. Amending the first formula to =CHOOSE(ROUND(MOD(A1,1)*10+1,0),"z","a","b","c","d ","e","f","g","h","i") gives the correct results. As you say, very odd indeed. I would also like to send best wishes to you (and everyone else) for the New Year. -- Regards Roger Govier "Bernard Liengme" wrote in message ... To help answer a question on this group, I wanted to convert the decimal part of a number (in the set 1, 1.1, 1.2, ...... 9.7, 9.8, 9.9 ) to a letter. Thus 2.1 would yield a, 2.2 would give b. I experimented with =CHOOSE(MOD(A1,1)*10+1,"z","a","b","c","d","e","f" ,"g","h","i") but it did not quite work (for example 3.8 gave 'g' and not 'h') So I tried =CHOOSE(MOD(A1*10,10)+1,"z","a","b","c","d","e","f ","g","h","i") and this worked. Odd since, at the integer level, =MOD(A1,1)*10+1 and =MOD(A1*10,10)+1 give the same result. Using the Evaluate Formula tool (I work with XL 2003) I was unable to see why my original formula did not work. =CHOOSE(MOD(3.8,1)*10+1,"z","a","b","c","d","e","f ","g","h","i") yields =CHOOSE(9,"z","a","b","c","d","e","f","g","h","i") but the next result is g not h Very odd!!! Happy New Year to all -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A rounding oddity
Requesting 30 decimal places is wasted effort. As documented, Excel will
display no more than 15 significant figures. If you ask for more, Excel pads them with zeros regardless of the actual values. On the other hand, it takes 17 significant figures to uniquely identify the binary representation of an IEEE double precision number (used by Excel and almost all other software). Therefore, to determine what is going on, you either need to use subtraction, such as =(MOD(A1*10,10)+1-9) (note the outer parentheses that prevent Excel from being overly "helpful" and returning zero when Bernard's formula isn't really 9), or you can use functions such as those at http://groups.google.com/group/micro...06871cf92f8465 Jerry "Roger Govier" wrote: Hi Bernard Yes, I get the same result as you with XL2003. With the series 2.0,2.1 etc through to 3.0 in A1:A11 I get the letter series z,a,b,b,c,e,f,g,g,i,z whereas with your second formula I get the correct series z,a,b,c,d,e,f,g,h,i,z Looking at the =MOD(A1,1)*10+1 part of the formula and the =MOD(A1*10,10)+1without the Choose function the return is the series of numbers 1,2,3,4,5,6,7,8,9,10,1 in both cases, with no detectable difference even when extending decimal places to 30. It therefore appears as though Choose is giving rise to the error. Amending the first formula to =CHOOSE(ROUND(MOD(A1,1)*10+1,0),"z","a","b","c","d ","e","f","g","h","i") gives the correct results. As you say, very odd indeed. I would also like to send best wishes to you (and everyone else) for the New Year. -- Regards Roger Govier "Bernard Liengme" wrote in message ... To help answer a question on this group, I wanted to convert the decimal part of a number (in the set 1, 1.1, 1.2, ...... 9.7, 9.8, 9.9 ) to a letter. Thus 2.1 would yield a, 2.2 would give b. I experimented with =CHOOSE(MOD(A1,1)*10+1,"z","a","b","c","d","e","f" ,"g","h","i") but it did not quite work (for example 3.8 gave 'g' and not 'h') So I tried =CHOOSE(MOD(A1*10,10)+1,"z","a","b","c","d","e","f ","g","h","i") and this worked. Odd since, at the integer level, =MOD(A1,1)*10+1 and =MOD(A1*10,10)+1 give the same result. Using the Evaluate Formula tool (I work with XL 2003) I was unable to see why my original formula did not work. =CHOOSE(MOD(3.8,1)*10+1,"z","a","b","c","d","e","f ","g","h","i") yields =CHOOSE(9,"z","a","b","c","d","e","f","g","h","i") but the next result is g not h Very odd!!! Happy New Year to all -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A rounding oddity
Hi John
I'm not sure what you mean here. The Mod(5.1,1)*10+1,0) returns 2, which Choose then correctly selects "a" from the series as a result. -- Regards Roger Govier "John Mac" wrote in message ... Roger Using this method: =CHOOSE(ROUND(MOD(A1,1)*10+1,0),"z","a","b","c","d ","e","f","g","h","i") how do you get to replace the decimal without losing the whole number (ie. 5.1 becomes 5a) "Roger Govier" wrote: Hi Bernard Yes, I get the same result as you with XL2003. With the series 2.0,2.1 etc through to 3.0 in A1:A11 I get the letter series z,a,b,b,c,e,f,g,g,i,z whereas with your second formula I get the correct series z,a,b,c,d,e,f,g,h,i,z Looking at the =MOD(A1,1)*10+1 part of the formula and the =MOD(A1*10,10)+1without the Choose function the return is the series of numbers 1,2,3,4,5,6,7,8,9,10,1 in both cases, with no detectable difference even when extending decimal places to 30. It therefore appears as though Choose is giving rise to the error. Amending the first formula to =CHOOSE(ROUND(MOD(A1,1)*10+1,0),"z","a","b","c","d ","e","f","g","h","i") gives the correct results. As you say, very odd indeed. I would also like to send best wishes to you (and everyone else) for the New Year. -- Regards Roger Govier "Bernard Liengme" wrote in message ... To help answer a question on this group, I wanted to convert the decimal part of a number (in the set 1, 1.1, 1.2, ...... 9.7, 9.8, 9.9 ) to a letter. Thus 2.1 would yield a, 2.2 would give b. I experimented with =CHOOSE(MOD(A1,1)*10+1,"z","a","b","c","d","e","f" ,"g","h","i") but it did not quite work (for example 3.8 gave 'g' and not 'h') So I tried =CHOOSE(MOD(A1*10,10)+1,"z","a","b","c","d","e","f ","g","h","i") and this worked. Odd since, at the integer level, =MOD(A1,1)*10+1 and =MOD(A1*10,10)+1 give the same result. Using the Evaluate Formula tool (I work with XL 2003) I was unable to see why my original formula did not work. =CHOOSE(MOD(3.8,1)*10+1,"z","a","b","c","d","e","f ","g","h","i") yields =CHOOSE(9,"z","a","b","c","d","e","f","g","h","i") but the next result is g not h Very odd!!! Happy New Year to all -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A rounding oddity
Hi Jerry
Many thanks for the response. Whilst I had always thought that there were only 15 significant places of decimal, I was very surprised that formatting allowed me to select 30 - I have never attempted to select that number of decimals before. I don't understand why it allows you to select above 15, but that's another matter. I follow what you are saying, and note your reply earlier in the thread (which was not available to me at the time of my posting). Thank you for the generosity of the code as published in the link you provided, and best wishes for 2007 -- Regards Roger Govier "Jerry W. Lewis" wrote in message ... Requesting 30 decimal places is wasted effort. As documented, Excel will display no more than 15 significant figures. If you ask for more, Excel pads them with zeros regardless of the actual values. On the other hand, it takes 17 significant figures to uniquely identify the binary representation of an IEEE double precision number (used by Excel and almost all other software). Therefore, to determine what is going on, you either need to use subtraction, such as =(MOD(A1*10,10)+1-9) (note the outer parentheses that prevent Excel from being overly "helpful" and returning zero when Bernard's formula isn't really 9), or you can use functions such as those at http://groups.google.com/group/micro...06871cf92f8465 Jerry "Roger Govier" wrote: Hi Bernard Yes, I get the same result as you with XL2003. With the series 2.0,2.1 etc through to 3.0 in A1:A11 I get the letter series z,a,b,b,c,e,f,g,g,i,z whereas with your second formula I get the correct series z,a,b,c,d,e,f,g,h,i,z Looking at the =MOD(A1,1)*10+1 part of the formula and the =MOD(A1*10,10)+1without the Choose function the return is the series of numbers 1,2,3,4,5,6,7,8,9,10,1 in both cases, with no detectable difference even when extending decimal places to 30. It therefore appears as though Choose is giving rise to the error. Amending the first formula to =CHOOSE(ROUND(MOD(A1,1)*10+1,0),"z","a","b","c","d ","e","f","g","h","i") gives the correct results. As you say, very odd indeed. I would also like to send best wishes to you (and everyone else) for the New Year. -- Regards Roger Govier "Bernard Liengme" wrote in message ... To help answer a question on this group, I wanted to convert the decimal part of a number (in the set 1, 1.1, 1.2, ...... 9.7, 9.8, 9.9 ) to a letter. Thus 2.1 would yield a, 2.2 would give b. I experimented with =CHOOSE(MOD(A1,1)*10+1,"z","a","b","c","d","e","f" ,"g","h","i") but it did not quite work (for example 3.8 gave 'g' and not 'h') So I tried =CHOOSE(MOD(A1*10,10)+1,"z","a","b","c","d","e","f ","g","h","i") and this worked. Odd since, at the integer level, =MOD(A1,1)*10+1 and =MOD(A1*10,10)+1 give the same result. Using the Evaluate Formula tool (I work with XL 2003) I was unable to see why my original formula did not work. =CHOOSE(MOD(3.8,1)*10+1,"z","a","b","c","d","e","f ","g","h","i") yields =CHOOSE(9,"z","a","b","c","d","e","f","g","h","i") but the next result is g not h Very odd!!! Happy New Year to all -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding | Excel Discussion (Misc queries) | |||
How do I correct rounding errors in Excel formulas? | Excel Worksheet Functions | |||
Rounding to the Nearest Eighth | Excel Discussion (Misc queries) | |||
Rounding | Excel Discussion (Misc queries) | |||
Banker's Rounding - need help! | Excel Discussion (Misc queries) |