Rounding to significant figures - converting an Excel formula for use in VBA

I don't think you've copied the original formula correctly.

Please Login or Register  to view this content.

The part in red is a complete formula. The subsequent ",VLOOKUP..." will throw an error.

WBD

Breaking it down:

Please Login or Register  to view this content.

Distilling this into VBA:

Please Login or Register  to view this content.

WBD

IMHO, the simplest (and probably most reliable) method is:

Function roundsig(x, n As Long) As Double
roundsig = WorksheetFunction.Text(x, "0." & String(n - 1, "0") & "E+0")
End Function

ERRATA.... I replaced VBA Format with Excel TEXT to fix an anomaly that I am still investigating.

I think the point of the function above is that it adjusts the number of decimal places to fit the precision of the number.

WBD

Originally Posted by WideBoyDixon

I think the point of the function above is that it adjusts the number of decimal places to fit the precision of the number.

I don't know what "function above" you are referring to.

And if mine, whether you are trying to confirm its behavior or question its correctness for the purpose of this thread.

To clarify....

The expression WorksheetFunction.Text(x, "0." & String(n - 1, "0") & "E+0") rounds the numeric value to the specified significant digits, not decimal places, just as the LOG-based expression purports to do.

ERRATA: I replaced VBA Format with Excel TEXT to fix an anomaly that I am still investigating.

The reason is: the Scientific format (0.000E+0, for example) shows a number of significant digits (4 in this example) to the left of the exponent ("E+0"), regardless of the magnitude.

So by rounding the Scientific format to n-1 decimal places, we are rounding the numeric value to n significant digits.

Refer to the image below and Excel file.

Although I use Excel formulas, I hope it is obvious how to write them in VBA (or use the function that I provided).

Both 123456789012345 (A2) and 0.123456789012345 (A3) have 15 significant digits.

And when formatted as Scientific with 3 decimal places (C1 and C2 minus one) and converted to a number, both are rounded to the first 4 significant digits (D2 and D3).

The formula in D2 is =--TEXT(A2, "0." & REPT("0",C2-1) & "E+0") .


-----
Aside (TMI?)....

I include 12345678901234500000 (A4) to make a subtle point.

Even though that rounds to the intended 15 significant digits due to formatting limitations in Excel, Excel cannot really represent an integer of that magnitude exactly due to limitations of the internal binary representation.

The exact decimal representation of the binary approximation is 12345678901234499584 (F4), which has 20 significant digits.


-----

I'm not questioning your function; I'm merely stating that the original formula in Excel:

Please Login or Register  to view this content.

Is geared to providing a sliding scale of significant digits based on the value in K2 whereas your function requires the precision to be passed as an argument.

WBD

Originally Posted by WideBoyDixon

I'm merely stating that the original formula [...] Is geared to providing a sliding scale of significant digits based on the value in K2 whereas your function requires the precision to be passed as an argument.

I'm not sure what you mean by "sliding scale". But if I understand you correctly, ironically it is just the opposite.

The OP's original formula and yours round only to 2 significant digits, intentionally. (The OP wrote: ``report a number to 2 significant figures``.)

That's why the OP's formula has the expression 2 - (1 + int(log(...))), which you simplified algebraically to 1 - int(log(...)).

I simply parameterized it so that my function works for any number of significant digits from 1 to 15.

No harm in that; the OP would simply use roundsig(K2/5, 2).

We can do the same thing with the OP's formula, replacing 2 with "n" and doing the same algebraic simplification that you did. Thus, your formula becomes:

Function roundsig1a(x, n As Long) As Double
roundsig1a = Round(x, n - 1 - Int(Log(Abs(x)) / Log(10)))
End Function

And FYI, all of that is independent of the magnitude of K2. That's the difference between significant digits and decimal places, in general.

I'm not sure why your post comes across as so angry? Here's results from the original formula:

Original Value	Calculation
1	0.2
10	2
100	20
0.02	0.004
0.004	0.0008
0.000006	0.0000012

It's clear that the number of decimal places varies according to the size of the argument; it's not fixed at 2 decimal places. The reason it does this is because it takes the base 10 log of the intended output and uses that to determine how many decimal places to round to. I'd just rather anyone stumbling on this thread in the future have an understanding of what the original function was all about. Whether the function fits the original requirement (which it doesn't - 2DP is only guaranteed for some values) is not really the issue; I'm more interested in what it *actually* does.

WBD

Originally Posted by WideBoyDixon

It's clear that the number of decimal places varies according to the size of the argument; it's not fixed at 2 decimal places.

I never said it was. I wrote: ``That's the difference between significant digits and decimal places``.

Originally Posted by WideBoyDixon

Whether the function fits the original requirement (which it doesn't - 2DP is only guaranteed for some values)

I never said anything about "guaranteeing" 2DP (sic). I wrote: ``The OP's original formula and yours round only to 2 significant digits, intentionally``.

-----

Anyway, enough about your misunderstandings! My TEXT function, when used as roundsig(Range("K2")/5,2), returns the same results as your Log expression (except for your defects below). And that should be all that matters.

The more important thing is to address the defects in your implementation, for the benefit of "wonderdunder".


1. If K2/5 results in a value with 3 significant digits (not decimal places) which ends in 5 and the next digit to the left is even, your implementation rounds down to the even value (usually *).

For example, if K2 is 0.01625 and K2/5 is 0.00325, your implementation returns 0.0032, whereas "wonderdunder's" Excel formula correctly returns 0.0033.

This is because VBA Round does so-called banker's rounding (round half to even), whereas Excel ROUND does "normal" rounding (round half up).

And "wonderdunder" made it clear that he is trying to implement the Excel expression ROUND((K2/5),2-(1+INT(LOG10(ABS(K2/5))))) in VBA.

(* I say "usually" because looks can be deceiving due to binary anomalies. For example, Round(0.00345,4) does indeed result in 0.0034, but Round(0.01725/5,4) results in 0.0035, even though 0.01725/5 appears to be 0.00345. The reason is: 0.01725/5 is actually infinitesimally larger than 0.00345. So we are no longer rounding exactly "half" (5).)


2. If K2/5 results in a value of 100 or more (i.e. K2 is 500 or more), your implementation returns an error.

This is because in that case, the decimal places parameter for the rounding function is -1 or more negative, and VBA Round does not support that.


The remedy for both defects is the same: use WorksheetFunction.Round instead of VBA Round, as "wonderdunder" so wisely tried to do. (He just got the syntax wrong.)

Alternatively, if "wonderdunder" only wants to round to 2 significant digits (not decimal places), the simplest form of my suggestion is:

Range("B2") = CDbl(WorksheetFunction.Text(Range("K2")/5, "0.0E+0"))

(CDbl is needed only if B2 might be formatted as General. In that case, without CDbl, Excel will display the value with Scientific format.)