excelvbasignificant-digits

Significant digits without ROUND

I need to show two significant digits, but it is important that the original data does not get lost.

Currently I have a macro that uses ROUND, however this changes the actual number instead of changing only the decimal places.

cell.Value = Round(cell, 1 - (1 + Int(Log(Abs(cell)) / Log(10))))

For other numbers I have

cell.NumberFormat = "#"

Then Excel shows only how many numbers I tell it to but I cannot get a variable number of digits, right?

Is there a way to combine these to so that excel will show two significant numbers?

Solution

  • What you can do is you can calculate how many digits you want to show as you did:

    Dim Digits As Long
Digits = 1 - (1 + Int(Log(Abs(Cell.Value)) / Log(10)))

    And then set the number format accordingly

    If Digits > 0 Then
    Cell.NumberFormat = "0." & String(Digits, "0")
Else
    Cell.NumberFormat = "#"
End If

    So if Digits is 1 it will use 0.0 as number format and if it is 2 it will use 0.00 as number format.

    So for example the following numbers will show as below

    enter image description here

    Note
    Don't use this solution if there are formulas in those cells you try to format. Because if the value changes because the result of the formula changes, then the number format will not change accordingly and stay the old one.
    Note that every cell will have set it's own number format.