[Solved] Removing decimal point from number

Discuss the spreadsheet application

[Solved] Removing decimal point from number

Postby rwwood » Fri Dec 27, 2013 3:36 am

I need to be able to remove the decimal point from a number without rounding it. In other words I need to change, for example, 1.22452 to 122452. The change has to be irrespective of the number of digits and can't drop either leading or trailing zeros. If it seems like a strange request, it's for use with currency exchange prices where for example the price of the Euro might be $1.22452, but the difference in prices is in "pips" which are always whole numbers. So, I might want to show the difference in price between $1.22452 and $1.22400 as 52 pips.

I'm not sure it can be done, but any help will be appreciated.
Last edited by rwwood on Sat Dec 28, 2013 8:50 am, edited 3 times in total.
LibreOffice Version 4.1.3.2, Kubuntu 13.10

OpenOffice 4.1.1 on Kubuntu 13.10, HSQLDB 2.3.2 split database
User avatar
rwwood
 
Posts: 41
Joined: Fri Dec 27, 2013 3:06 am

Re: Removing decimal point from number

Postby coray80 » Fri Dec 27, 2013 5:08 am

OpenOffice 4.1.2 on Windows 7
If your question has been answered please add [solved] to the title by using the edit button at your first post
coray80
Volunteer
 
Posts: 357
Joined: Thu Mar 01, 2012 6:41 am

Re: Removing decimal point from number

Postby rwwood » Fri Dec 27, 2013 8:01 am

This would work if all numbers were 6 digit, but some are only 4 or 5 digit.
LibreOffice Version 4.1.3.2, Kubuntu 13.10

OpenOffice 4.1.1 on Kubuntu 13.10, HSQLDB 2.3.2 split database
User avatar
rwwood
 
Posts: 41
Joined: Fri Dec 27, 2013 3:06 am

Re: Removing decimal point from number

Postby coray80 » Fri Dec 27, 2013 9:09 am

Sorry I didn't catch the 'irrespective of the number of digits'.
This seems to work
Code: Select all   Expand viewCollapse view
=A1*(10^(LEN(A1)-FIND(".";A1)))
OpenOffice 4.1.2 on Windows 7
If your question has been answered please add [solved] to the title by using the edit button at your first post
coray80
Volunteer
 
Posts: 357
Joined: Thu Mar 01, 2012 6:41 am

Re: Removing decimal point from number

Postby rwwood » Sat Dec 28, 2013 9:05 am

This would definitely be the solution were it not for the fact that I have conditional formatting in play that sets the number of decimals based on the content of another cell. The problem arises if the number typed in A1 (in the forumla above) is conditionally formatted to have 4 decimals for example. If 1.234 is typed in, it shows up as 1.2340 in the cell but remains 1.234 in the formula bar and is therefore treated as a number with only 3 decimal places, which in turn throws off the calculation. So, as an example of the error that can occur, if the two numbers that are to have the decimal point removed and subtrated one from the other are:1.234 and 1.2222, what gets returned is 1234 - 12222 = -10988 rather than the correct answer: 12340 - 12222 = 118
LibreOffice Version 4.1.3.2, Kubuntu 13.10

OpenOffice 4.1.1 on Kubuntu 13.10, HSQLDB 2.3.2 split database
User avatar
rwwood
 
Posts: 41
Joined: Fri Dec 27, 2013 3:06 am

Re: Removing decimal point from number

Postby keme » Sat Dec 28, 2013 12:22 pm

If we know the "pip depth", aka the number of significant decimals, that makes it a bit easier than the suggestion from coray80: =ROUND(A1*10^PipDepth;0)
AFAIK, pip depth will normally be 4, but in some cases (for some currencies) it is common to use 2. If you want higher precision, the convention is to use "fractional pips".
Please correct me if I am mistaken (it's been a while since I took those economy classes, and currency trading was not the most important subject covered).

If you want to use fractional pips, just change the final zero to 1 in the suggested formula.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3404
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: [Solved] Removing decimal point from number

Postby acknak » Sat Dec 28, 2013 5:19 pm

I must be missing something here. Why do arithmetic with the pips? It seems that you're taking steps to remove the decimal point (and magnitude) and then having to worry about the magnitude ever after. Seems like a recipe for wrong answers!

Can you not do the arithmetic on the natural numeric values and then display the answer as pips?
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Removing decimal point from number

Postby rwwood » Sat Dec 28, 2013 10:23 pm

keme wrote:If we know the "pip depth", aka the number of significant decimals, that makes it a bit easier than the suggestion from coray80: =ROUND(A1*10^PipDepth;0)
AFAIK, pip depth will normally be 4, but in some cases (for some currencies) it is common to use 2. If you want higher precision, the convention is to use "fractional pips".
Please correct me if I am mistaken (it's been a while since I took those economy classes, and currency trading was not the most important subject covered).

If you want to use fractional pips, just change the final zero to 1 in the suggested formula.


This worked out fine. Thanks
LibreOffice Version 4.1.3.2, Kubuntu 13.10

OpenOffice 4.1.1 on Kubuntu 13.10, HSQLDB 2.3.2 split database
User avatar
rwwood
 
Posts: 41
Joined: Fri Dec 27, 2013 3:06 am

Re: [Solved] Removing decimal point from number

Postby RusselB » Sun Jan 11, 2015 7:45 am

While this is marked as solved, I thought I'd see if I could figure out a way to not have to know the PipDepth.
Technically I found a solution
Code: Select all   Expand viewCollapse view
=VALUE(TEXT(INT(G1);"#")&TEXT((G1-INT(G1))*10^(LEN(G1-INT(G1)));"#"))
however, due to the accuracy of the internal calculations of Calc this may not look correct.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6355
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] Removing decimal point from number

Postby KMullins » Sun Jan 11, 2015 7:21 pm

Hi All, I'm a noobie, I was browsing the topics to try and find one that deals with my question. This seems to be the closest one. I have two functions I'm working on to build a spread sheet using data from a GPS unit. I need to round the miles up to the nearest tens and drop the numbers after the decimal in an the entire column of C, D and E.

IE 208.943 to 209

Then I need to add the numbers in column C according to the name in column B and report it to a row with the same name as column C.

I'll post a little of the data. The columns are Date, State, total miles, toll miles, non toll miles.

10/3/2014 INDIANA 309.1935 0 309.1935
10/3/2014 ILLINOIS 152.7463 0 152.7463
10/3/2014 MISSOURI 10.2342 0 10.2342
10/4/2014 MISSOURI 106.9818 0 106.9818
10/4/2014 ARKANSAS 261.7863 0 261.7863
10/5/2014 ARKANSAS 45.5597 0 45.5597
10/5/2014 TEXAS 164.3582 0 164.3582
10/6/2014 TEXAS 116.4006 0 116.4006
10/6/2014 OKLAHOMA 253.2843 86.0261 167.2582
10/7/2014 OKLAHOMA 64.459 0 64.459
10/7/2014 KANSAS 229.6013 0 229.6013
10/7/2014 NEBRASKA 102.6026 0 102.6026
10/8/2014 NEBRASKA 0.3634 0 0.3634
10/10/2014 NEBRASKA 0.217 0 0.217
10/13/2014 NEBRASKA 101.0836 0 101.0836
10/13/2014 KANSAS 145.7272 0 145.7272

At the bottom of this list I would list all the states and like the totals of those miles from those states...

Any help would be appreciated. I was playing with the round up function but I don't know how to get it to call up the value that's already in the cell to do the calculation.
OpenOffice 3.4.1 on Windows7
KMullins
 
Posts: 1
Joined: Sun Jan 11, 2015 7:09 pm

Re: [Solved] Removing decimal point from number

Postby RusselB » Mon Jan 12, 2015 2:43 am

Not really related to the actual original question, but I'll leave the decision to leave or move your post to a moderator.
Getting to your query in column F enter
Code: Select all   Expand viewCollapse view
=roundup(C1/10;1)*10
for the numbers in D & E, use the INT function
Then I need to add the numbers in column C according to the name in column B and report it to a row with the same name as column C.
and
At the bottom of this list I would list all the states and like the totals of those miles from those states...

Look at SUMIF or SUMPRODUCT
I don't know how to get it to call up the value that's already in the cell to do the calculation.

Simply, you can't... you need to use an additional helper column (or possibly more than one)
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6355
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 13 guests