Decimal to Whole number

Try this

Formula:

Please Login or Register  to view this content.

v	A	B	C
1	0.0023	0.0022	1

Originally Posted by SarahCohen

I'll have numbers in two different cell like:
.0023-.0022 = .0001, but I'll want the 3rd cell to do the calculation, then have it show as 1, instead of .0001
I"m not sure how to plug in something like
=VALUE(SUBSTITUTE(A2,".","")) or =--SUBSTITUTE(A1,".","") into a cell formula to do both the calculation and come out with the correct number.

If you mean that, for example, A1 is 0.0023 and A2 is 0.0022, and you want a formula in A3 that results in the numerical value 1, then following the previous suggestions, we might write the following into A3:

=--SUBSTITUTE(A1-A2,".","")

But that is the precisely an example of the problem that I forewarned about.

The short answer is: if you want the result of A1-A2 to be accurate to 4 decimal places, explicitly round the calculation. So the formula should be:

=--SUBSTITUTE(ROUND(A1-A2,4),".","")

On second thought, IMHO, the better solution is:

=ROUND(A1*10000,0) - ROUND(A2*10000,0)

The long explanation is....

The result of the original SUBSTITUTE formula in A3 will be appear to be 10000000000 (!) when A3 is formatted as General.

And the actual value will be 9999999999.99998, which you can see when A3 is formatted as Number with 5 decimal places.

The reason, in part, is because 0.0023-0.0022 returns the value 0.0000999999999999998, not 0.0001.

This is due to well-documented anomalies of binary arithmetic with decimal fractions. In a nutshell, most decimal fractions cannot be represented in the binary form that Excel uses internally, which is called 64-bit binary floating-point.

So ostensibly, we might expect SUBSTITUTE(A1-A2,".","") to return 00000999999999999998, resulting in the numeric value 999999999999998.

But in fact, the formula =""&A1-A2 returns the string "9.99999999999998E-05". So SUBSTITUTE(A1-A2,".","") returns the string "999999999999998E-05", which is converted to the numeric value 9999999999.99998 .

Floating point errors are usually in the 14th and 15th decimal places (with accumulated errors rarely reaching the 13th decimal place). Any rounding to 10 decimal places will be a safe bet.
This one will work as well, though I guess ROUND is a bit faster than TEXT, in case there were tens of thousands of formulas requiered.

Formula:

Please Login or Register  to view this content.

All of the OP's examples are <1.

Substracting numbers with several orders of magnitude of difference will effect the needed significant digits:
1,000,000,000 - 0.0000001 will require more digits than excel can work with, it has only 7 decimal places, though; add this to the floating point calculation error, and of course we'll find failing pairs. As the numbers differences get smaller, the floating point rounding error will move more towards the right.

Round to 10 places should be compared with round to 9 or 8 places, not 3 places; rounding to 3 or 4 decimal places won't catch pairs like:
0.00006-0.00004, or 0.00020-0.00015, which are much more likely to occur, as the OP has given numbers like .023045 and 0.00045, and clearly stating in his initial request that "any number of decimal places become a whole number", and all the examples are >0 and <1, so we want to catch the greatest number of decimal places, catching 3 or 4 is reasonable not enough.

Even 10 decimal places might not be enough, as Jose Augusto points out, Excel has limitations, only 15 digits can be used, trying to play safe, I suggested to remove 5 places due to floating point errors and round to 10 places; rounding to 1,2,3,4, or 5 places will take out many pairs, considering the OP has numbers with 6 decimal places.

Originally Posted by AlKey

Try this

Formula:

Please Login or Register  to view this content.

Hi AlKey,

Your formula worked for the example above, but if I have a number such as:

119.7700 - 119.6800, it gives the result as 0.09, instead of 9. Is there a way to make the resulting answer to be 9 also in this type of situation?

SarahCohen,

Why not post a workbook with many examples of what you have and what you expect.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Here is another one

Formula:

Please Login or Register  to view this content.