Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 most significant digits
Hi there,
I'm using the following code to get the 3 most significant numbers on a cell. The cell properties is set to General ------------------------------------------------------------------------------------ For Each c In Range("F9", "F25") c.Offset(0, 3).Value = Application.Round(c.Value, 2 - Int(Log10(c.Value))) next .................................................. .................................................. ....... Static Function Log10(x) Log10 = Log(x) / Log(10#) End Function --------------------------------------------------------------------------------------- it works except when the value is 0.30 it shows 0.3 How can I set the properties or change the code so it shows always the 3 most significant digits? 0.3215 0.322 0.3000 0.300 80.84 80.8 530.15 530 and so on Any help is appreciated. Gaba |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 most significant digits
Your code does not *get* the 3 most significant digits... it *rounds* your
number to 3 significant digits. Since your cell's format is set to General, numbers are displayed as numbers... and numbers do not have trailing zeroes. The only way you will be able to display the trailing zeroes is by changing the cell's format to Text or to modify the code to have it change the cell's format on the fly to a custom format that displays your 3 significant figures. If you would like the latter, post back showing us your actual code (copy/paste it into your message, don't try to retype it). By the way, will it ever be possible for your numbers to be in e-notation (such as 1.2345e67) and, if so, would you want the number in front of the "e" to be rounded to 3 significant digits? -- Rick (MVP - Excel) "Gaba" wrote in message ... Hi there, I'm using the following code to get the 3 most significant numbers on a cell. The cell properties is set to General ------------------------------------------------------------------------------------ For Each c In Range("F9", "F25") c.Offset(0, 3).Value = Application.Round(c.Value, 2 - Int(Log10(c.Value))) next .................................................. .................................................. ...... Static Function Log10(x) Log10 = Log(x) / Log(10#) End Function --------------------------------------------------------------------------------------- it works except when the value is 0.30 it shows 0.3 How can I set the properties or change the code so it shows always the 3 most significant digits? 0.3215 0.322 0.3000 0.300 80.84 80.8 530.15 530 and so on Any help is appreciated. Gaba |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 most significant digits
Hi Gaba,
You can either manually set the number format of the cells on the worksheet or with code you can set the number format of the cell as follows:- Tthe following code will format the entire range. Range("F9", "F25").Offset(0, 3).NumberFormat = "0.000" The following code will format each cell individually after the result is assigned to it. For Each c In Range("F9", "F25") c.Offset(0, 3).Value = Application.Round(c.Value, 2 - Int(Log10(c.Value))) c.Offset(0, 3).NumberFormat = "0.000" Next -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 most significant digits
Hi again Gaba,
I think that you might need to precede log10 with worksheetfunction also. WorksheetFunction.Log10 -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
3 most significant digits
Hello,
Leading or trailing zeros are no significant digits. A correct and efficient solution you can find he http://sulprobil.com/html/nsig.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
significant digits | Excel Worksheet Functions | |||
Significant digits | Excel Worksheet Functions | |||
significant digits for decimals | Excel Worksheet Functions | |||
Calculations and significant digits | Excel Programming | |||
Calculations and significant digits | Excel Programming |