[Solved] Removing decimal point from number

[rwwood]

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.

[coray80]

Code: Select all

=A1*100000

[rwwood]

This would work if all numbers were 6 digit, but some are only 4 or 5 digit.

[coray80]

Sorry I didn't catch the 'irrespective of the number of digits'.
This seems to work

Code: Select all

=A1*(10^(LEN(A1)-FIND(".";A1)))

[rwwood]

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

[keme]

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.

[acknak]

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?

[rwwood]

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

[RusselB]

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

=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.

[KMullins]

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.

[RusselB]

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

=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)