• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formatting numeric value with decimal points

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I need help for formatting Numeric value.

I faced this issue while working on the “Convert” function.

While using this function the result generated were such as 1.001, 1.0001, 1.00000004 or 1.000000000005.

I have to keep increasing the Decimal to see the exact value, if I did not increase the Decimal, the values will get populated as 1 only.

There were more than 230 cells which I have to format manually to see the exact value.
Some of the cells required decimal points up to 22 or some of them required just 1 Decimal point.

Though I completed this formatting & the issue is not troubling me now.
I was thinking can we automate this with Formula or VBA.

For example, the value 1.00005 will need 5 Decimal points to show the exact result.
Less decimals will show the value as 1 only.

If we can design a code, which will check if the value is not whole number and right most value after the decimal is 0 then keep increasing the decimal.

For example, we can have a value as 5 only, this does not require decimal points, but the values 5.0001 will require 4 decimal points.

This is not a real life problem for me, but I was wondering if anyone here has worked on such issue earlier.

Any help is welcome.

The only vague idea comes to my mind is, Do While Loop may be useful here.
If the value after decimal point is equal to 0 then keep increasing the decimal till it is greater than 0.

But I do know how to I write this in a code and also, what if the values is a whole number, does not require decimal at all !
 

Attachments

@Debraj , sorry for the confusion.

We have Cell A1, B1, and C1.
By default the format of the cell is, Number, Decimal places 0.

Now we have following values in order.
A1 =10/100
B1 =10/1000
C1 =10/10000

Since the format is decimal places 0, the visible value in all the cells will be 0.
I have to set decimal points to 1,2, and 3 respectively.

In our example I set the format manually, it is possible that A1 might require 10 decimal, B1 2 decimal, C1 5 Decimal.
So how do I set that with VBA.
 
Hi, ThrottleWorks!

I'm bad news. You can't do that. Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Formatting numeric value with decimal points - DecimalFormatting (for ThrottleWorks at chandoo.org).xlsm

I added a lot of lines simulating the decimal increase, and the result is:

Check column A. I changed format to currency with the max digits available, 30, but from the 18th decimal in advance the value actually stored is rounded as you may see in your C column. I tried a few things (string value in column B), an UDF to use the conversion to decimal number (column F), played a bit in column G with strings again... and no success.

Hope it helps, at least to make you forget about the 22 decimal digits precision.

Regards!
 
@SirJB7 , Sir you are never a bad news for us !
The link is not accessible in the office, I will check it at home.

Your file will certainly help me.

Thanks for the help, have a nice day ahead. :)
 
Sir, I have downloaded the file, please delet from here.

Checking the file, once again thanks a lot.

P.S. - Sir, I checked the file, it is working absolutely fine.
Yes, the limitation by excel is 18 decimal places, but should not bother me much.

In the attached file, I used Convert function to translate Inch with Miles.

If I am using your function and as long as the decimals are < 19 I do not have to worry about formatting.
 

Attachments

Last edited:
Hi All ,

I think the point about 18 decimal places may not be correct ; Excel represents numbers to 15 significant digits.

Thus a number like 0.00000000000000009 may have 17 decimal places , but try this number and see what happens :

0.00100000000000009

Now try with the following two numbers :

0.01000000000000009

0.10000000000000009

The number of significant digits varies in each case ; in the last case it is more than 15 , which is beyond Excel's permissible limit.

Narayan
 
@NARAYANK991
Hi!
Thanks for the catch, that's correct, I guided for the last digit in the sample file and in this case 16-18th digits just happen to be a 1... but that doesn't give any certainty: 15 is the correct figure.
Regards!
 
Hi, ThrottleWorks!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top