Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract decimal and convert to integer
Hi,
Yesterday a question was posted in which the OP wanted to take the number 101.25 and extract the decimal 0.25 and convert that into an integer 25. A one off solution is simple, for example =MID(D1,3,LEN(D1))+0 or =(A1-(TRUNC(A1)))*100 and of course a modulus/multiplication solution. But none of these are generic for longer decimals so I set out to find a generic solution for any number length. This works perfectly for 101.25 and for any number to the left of the decimal point =($A$1-TRUNC($A$1))*(10^(LEN(($A$1-TRUNC($A$1)))-2)) But add any extra decimals and it can fail. For example 100.256 works perfectly because (i think) it must have a precise decimal/binary conversion but 101.257 falls over because the decimal portion is actually 0.257000000000005 so my formula that raises the (number*10^length of decimal bit) fails. I am missing something blindingly obvious so can anyone help me with a mathmatical (not a text fiddle) to this problem that will convert any number irrespective of the number of decimals. I haven't tried 'precision as displayed' because I instinctively don't like it Mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract decimal and convert to integer
Suppose the number is in A1
Then =ROUND(A1,0) will give you the integer part = (A1 - ROUND(A1,0)) will give you the decimal part Multiply this by 100 and round again as in step 1 You can use ROUNDDOWN() if you do not want rounding instead of ROUND() Let me if this is what you were looking for "Mike H" wrote: Hi, Yesterday a question was posted in which the OP wanted to take the number 101.25 and extract the decimal 0.25 and convert that into an integer 25. A one off solution is simple, for example =MID(D1,3,LEN(D1))+0 or =(A1-(TRUNC(A1)))*100 and of course a modulus/multiplication solution. But none of these are generic for longer decimals so I set out to find a generic solution for any number length. This works perfectly for 101.25 and for any number to the left of the decimal point =($A$1-TRUNC($A$1))*(10^(LEN(($A$1-TRUNC($A$1)))-2)) But add any extra decimals and it can fail. For example 100.256 works perfectly because (i think) it must have a precise decimal/binary conversion but 101.257 falls over because the decimal portion is actually 0.257000000000005 so my formula that raises the (number*10^length of decimal bit) fails. I am missing something blindingly obvious so can anyone help me with a mathmatical (not a text fiddle) to this problem that will convert any number irrespective of the number of decimals. I haven't tried 'precision as displayed' because I instinctively don't like it Mike |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract decimal and convert to integer
Hi,
Thanks for that but it's not generic. You have to manually decide to multiply by 100 which is fine for nnn.25 but isn't for nnn.256 for that you must Multiply by 1000. That has been the tricky bit for me working out in the formula what to multiply by. Mike "Sheeloo" wrote: Suppose the number is in A1 Then =ROUND(A1,0) will give you the integer part = (A1 - ROUND(A1,0)) will give you the decimal part Multiply this by 100 and round again as in step 1 You can use ROUNDDOWN() if you do not want rounding instead of ROUND() Let me if this is what you were looking for "Mike H" wrote: Hi, Yesterday a question was posted in which the OP wanted to take the number 101.25 and extract the decimal 0.25 and convert that into an integer 25. A one off solution is simple, for example =MID(D1,3,LEN(D1))+0 or =(A1-(TRUNC(A1)))*100 and of course a modulus/multiplication solution. But none of these are generic for longer decimals so I set out to find a generic solution for any number length. This works perfectly for 101.25 and for any number to the left of the decimal point =($A$1-TRUNC($A$1))*(10^(LEN(($A$1-TRUNC($A$1)))-2)) But add any extra decimals and it can fail. For example 100.256 works perfectly because (i think) it must have a precise decimal/binary conversion but 101.257 falls over because the decimal portion is actually 0.257000000000005 so my formula that raises the (number*10^length of decimal bit) fails. I am missing something blindingly obvious so can anyone help me with a mathmatical (not a text fiddle) to this problem that will convert any number irrespective of the number of decimals. I haven't tried 'precision as displayed' because I instinctively don't like it Mike |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract decimal and convert to integer
How about?
=--MID(TEXT(A1,"General"),FIND(".",TEXT(A1,"General") )+1,15) -- Regards, Peo Sjoblom "Mike H" wrote in message ... Hi, Thanks for that but it's not generic. You have to manually decide to multiply by 100 which is fine for nnn.25 but isn't for nnn.256 for that you must Multiply by 1000. That has been the tricky bit for me working out in the formula what to multiply by. Mike "Sheeloo" wrote: Suppose the number is in A1 Then =ROUND(A1,0) will give you the integer part = (A1 - ROUND(A1,0)) will give you the decimal part Multiply this by 100 and round again as in step 1 You can use ROUNDDOWN() if you do not want rounding instead of ROUND() Let me if this is what you were looking for "Mike H" wrote: Hi, Yesterday a question was posted in which the OP wanted to take the number 101.25 and extract the decimal 0.25 and convert that into an integer 25. A one off solution is simple, for example =MID(D1,3,LEN(D1))+0 or =(A1-(TRUNC(A1)))*100 and of course a modulus/multiplication solution. But none of these are generic for longer decimals so I set out to find a generic solution for any number length. This works perfectly for 101.25 and for any number to the left of the decimal point =($A$1-TRUNC($A$1))*(10^(LEN(($A$1-TRUNC($A$1)))-2)) But add any extra decimals and it can fail. For example 100.256 works perfectly because (i think) it must have a precise decimal/binary conversion but 101.257 falls over because the decimal portion is actually 0.257000000000005 so my formula that raises the (number*10^length of decimal bit) fails. I am missing something blindingly obvious so can anyone help me with a mathmatical (not a text fiddle) to this problem that will convert any number irrespective of the number of decimals. I haven't tried 'precision as displayed' because I instinctively don't like it Mike |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract decimal and convert to integer
On Fri, 19 Sep 2008 11:28:01 -0700, Mike H
wrote: Hi, Yesterday a question was posted in which the OP wanted to take the number 101.25 and extract the decimal 0.25 and convert that into an integer 25. A one off solution is simple, for example =MID(D1,3,LEN(D1))+0 or =(A1-(TRUNC(A1)))*100 and of course a modulus/multiplication solution. But none of these are generic for longer decimals so I set out to find a generic solution for any number length. This works perfectly for 101.25 and for any number to the left of the decimal point =($A$1-TRUNC($A$1))*(10^(LEN(($A$1-TRUNC($A$1)))-2)) But add any extra decimals and it can fail. For example 100.256 works perfectly because (i think) it must have a precise decimal/binary conversion but 101.257 falls over because the decimal portion is actually 0.257000000000005 so my formula that raises the (number*10^length of decimal bit) fails. I am missing something blindingly obvious so can anyone help me with a mathmatical (not a text fiddle) to this problem that will convert any number irrespective of the number of decimals. I haven't tried 'precision as displayed' because I instinctively don't like it Mike I don't think you can do it using worksheet functions without doing a "text fiddle", do, at least in part, to the reason you mention. But try: =REPLACE(A1,1,FIND(".",A1),"") or =--REPLACE(A1,1,FIND(".",A1),"") to do this using a "text fiddle". --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract decimal and convert to integer
This one has my brain box buzzing, can anyone come up with a formula? And
not using strings? (and by "strings" I mean text, not higher spatial dimensions) "Mike H" wrote: Hi, Thanks for that but it's not generic. You have to manually decide to multiply by 100 which is fine for nnn.25 but isn't for nnn.256 for that you must Multiply by 1000. That has been the tricky bit for me working out in the formula what to multiply by. Mike "Sheeloo" wrote: Suppose the number is in A1 Then =ROUND(A1,0) will give you the integer part = (A1 - ROUND(A1,0)) will give you the decimal part Multiply this by 100 and round again as in step 1 You can use ROUNDDOWN() if you do not want rounding instead of ROUND() Let me if this is what you were looking for "Mike H" wrote: Hi, Yesterday a question was posted in which the OP wanted to take the number 101.25 and extract the decimal 0.25 and convert that into an integer 25. A one off solution is simple, for example =MID(D1,3,LEN(D1))+0 or =(A1-(TRUNC(A1)))*100 and of course a modulus/multiplication solution. But none of these are generic for longer decimals so I set out to find a generic solution for any number length. This works perfectly for 101.25 and for any number to the left of the decimal point =($A$1-TRUNC($A$1))*(10^(LEN(($A$1-TRUNC($A$1)))-2)) But add any extra decimals and it can fail. For example 100.256 works perfectly because (i think) it must have a precise decimal/binary conversion but 101.257 falls over because the decimal portion is actually 0.257000000000005 so my formula that raises the (number*10^length of decimal bit) fails. I am missing something blindingly obvious so can anyone help me with a mathmatical (not a text fiddle) to this problem that will convert any number irrespective of the number of decimals. I haven't tried 'precision as displayed' because I instinctively don't like it Mike |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract decimal and convert to integer
I am assuming you know how Excel stores floating point numbers. IF not then
pl. reade http://www.cpearson.com/excel/rounding.htm. Since there is no way to determine the no. of places after decimal any number has in the internal storage, it is not possible to get what you are trying. You can try to get the LEN of the decimal part but it usually returns 17 but is not reliable... "Mike H" wrote: Hi, Thanks for that but it's not generic. You have to manually decide to multiply by 100 which is fine for nnn.25 but isn't for nnn.256 for that you must Multiply by 1000. That has been the tricky bit for me working out in the formula what to multiply by. Mike "Sheeloo" wrote: Suppose the number is in A1 Then =ROUND(A1,0) will give you the integer part = (A1 - ROUND(A1,0)) will give you the decimal part Multiply this by 100 and round again as in step 1 You can use ROUNDDOWN() if you do not want rounding instead of ROUND() Let me if this is what you were looking for "Mike H" wrote: Hi, Yesterday a question was posted in which the OP wanted to take the number 101.25 and extract the decimal 0.25 and convert that into an integer 25. A one off solution is simple, for example =MID(D1,3,LEN(D1))+0 or =(A1-(TRUNC(A1)))*100 and of course a modulus/multiplication solution. But none of these are generic for longer decimals so I set out to find a generic solution for any number length. This works perfectly for 101.25 and for any number to the left of the decimal point =($A$1-TRUNC($A$1))*(10^(LEN(($A$1-TRUNC($A$1)))-2)) But add any extra decimals and it can fail. For example 100.256 works perfectly because (i think) it must have a precise decimal/binary conversion but 101.257 falls over because the decimal portion is actually 0.257000000000005 so my formula that raises the (number*10^length of decimal bit) fails. I am missing something blindingly obvious so can anyone help me with a mathmatical (not a text fiddle) to this problem that will convert any number irrespective of the number of decimals. I haven't tried 'precision as displayed' because I instinctively don't like it Mike |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract decimal and convert to integer
Oops! I missed the text fiddle. Can't be done without it though
-- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... How about? =--MID(TEXT(A1,"General"),FIND(".",TEXT(A1,"General") )+1,15) -- Regards, Peo Sjoblom "Mike H" wrote in message ... Hi, Thanks for that but it's not generic. You have to manually decide to multiply by 100 which is fine for nnn.25 but isn't for nnn.256 for that you must Multiply by 1000. That has been the tricky bit for me working out in the formula what to multiply by. Mike "Sheeloo" wrote: Suppose the number is in A1 Then =ROUND(A1,0) will give you the integer part = (A1 - ROUND(A1,0)) will give you the decimal part Multiply this by 100 and round again as in step 1 You can use ROUNDDOWN() if you do not want rounding instead of ROUND() Let me if this is what you were looking for "Mike H" wrote: Hi, Yesterday a question was posted in which the OP wanted to take the number 101.25 and extract the decimal 0.25 and convert that into an integer 25. A one off solution is simple, for example =MID(D1,3,LEN(D1))+0 or =(A1-(TRUNC(A1)))*100 and of course a modulus/multiplication solution. But none of these are generic for longer decimals so I set out to find a generic solution for any number length. This works perfectly for 101.25 and for any number to the left of the decimal point =($A$1-TRUNC($A$1))*(10^(LEN(($A$1-TRUNC($A$1)))-2)) But add any extra decimals and it can fail. For example 100.256 works perfectly because (i think) it must have a precise decimal/binary conversion but 101.257 falls over because the decimal portion is actually 0.257000000000005 so my formula that raises the (number*10^length of decimal bit) fails. I am missing something blindingly obvious so can anyone help me with a mathmatical (not a text fiddle) to this problem that will convert any number irrespective of the number of decimals. I haven't tried 'precision as displayed' because I instinctively don't like it Mike |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract decimal and convert to integer
Apologies for the late response, I had to do some work!!
Thanks for all the responses. I think if I ever did have to solve this as opposed to a purely academic exercise I'd go with the text solutions offered by Peo & Ron but remain of the view that it could be done using math but am happy to accept it's beyond me. At least I understand now why nn.128, nn.256 etc work for my solution it because they do have a precise binary conversion and yes Thanks Sheeloo I do know how Excel stores numbers. Mike H "Ron Rosenfeld" wrote: On Fri, 19 Sep 2008 11:28:01 -0700, Mike H wrote: Hi, Yesterday a question was posted in which the OP wanted to take the number 101.25 and extract the decimal 0.25 and convert that into an integer 25. A one off solution is simple, for example =MID(D1,3,LEN(D1))+0 or =(A1-(TRUNC(A1)))*100 and of course a modulus/multiplication solution. But none of these are generic for longer decimals so I set out to find a generic solution for any number length. This works perfectly for 101.25 and for any number to the left of the decimal point =($A$1-TRUNC($A$1))*(10^(LEN(($A$1-TRUNC($A$1)))-2)) But add any extra decimals and it can fail. For example 100.256 works perfectly because (i think) it must have a precise decimal/binary conversion but 101.257 falls over because the decimal portion is actually 0.257000000000005 so my formula that raises the (number*10^length of decimal bit) fails. I am missing something blindingly obvious so can anyone help me with a mathmatical (not a text fiddle) to this problem that will convert any number irrespective of the number of decimals. I haven't tried 'precision as displayed' because I instinctively don't like it Mike I don't think you can do it using worksheet functions without doing a "text fiddle", do, at least in part, to the reason you mention. But try: =REPLACE(A1,1,FIND(".",A1),"") or =--REPLACE(A1,1,FIND(".",A1),"") to do this using a "text fiddle". --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Your query
I have also tried to get a formula to work on this but failed.
I found a simple one just to extract the decimal places. =MOD(A1,1) for 12.123 it give .123 for 12.1 it gives .1 Irrespective of size of decimal places. Now just select the new column with formulas and change it to values. Copy, Paste special, values. Select data, text to columns, delimiter="." And you get what you want. Hope this helps. Aziz PS: I know I am late (its been one year since it was asked). Mike wrote: extract decimal and convert to integer 19-Sep-08 Hi, Yesterday a question was posted in which the OP wanted to take the number 101.25 and extract the decimal 0.25 and convert that into an integer 25. A one off solution is simple, for example =MID(D1,3,LEN(D1))+0 or =(A1-(TRUNC(A1)))*100 and of course a modulus/multiplication solution. But none of these are generic for longer decimals so I set out to find a generic solution for any number length. This works perfectly for 101.25 and for any number to the left of the decimal point =($A$1-TRUNC($A$1))*(10^(LEN(($A$1-TRUNC($A$1)))-2)) But add any extra decimals and it can fail. For example 100.256 works perfectly because (i think) it must have a precise decimal/binary conversion but 101.257 falls over because the decimal portion is actually 0.257000000000005 so my formula that raises the (number*10^length of decimal bit) fails. I am missing something blindingly obvious so can anyone help me with a mathmatical (not a text fiddle) to this problem that will convert any number irrespective of the number of decimals. I haven't tried 'precision as displayed' because I instinctively don't like it Mike EggHeadCafe - Software Developer Portal of Choice WPF DataGrid Custom Paging and Sorting http://www.eggheadcafe.com/tutorials...tom-pagin.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
integer with decimal display | Excel Discussion (Misc queries) | |||
caused - an integer or decimal number may be required | Excel Discussion (Misc queries) | |||
how to split one decimal pt integer off & to next columnin excel? | Excel Discussion (Misc queries) | |||
excel-enter integer number and get two decimal places | Excel Discussion (Misc queries) | |||
Excel ActiveX Scrollbar - change from integer to decimal | Excel Discussion (Misc queries) |