1

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?

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
user17269242
  • 129
  • 8
  • 2
    You could use a scientific format like 0.0E+00 showing one decimal place, but that may not be the answer you want. – Tom Sharpe Jun 12 '23 at 07:11

1 Answers1

3

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 1
    Independant from PEH 's valid answer, one should keep in mind that rounding in VBA differs from Excel function as VBA uses so called *banker's rounding* (= midpoint value 5 always rounds to the nearest even number). - *Banker's rounding* is a standard form of rounding used in financial and statistical operations in order to minimize significant rounding errors over multiple rounding operations by consistently rounding midpoint values in a single direction. See [Round...](https://stackoverflow.com/questions/46850958/how-excel-vba-rounds-doubles-to-integers/48042374#48042374) – T.M. Jun 12 '23 at 16:43