Format to n significant digits?

[Jennifer Murphy]

Is there a way to *format* a number to N significant digits?

For N=2, the data on the left would get formatted as shown on the
right:

data display
0.0002468 0.00025
0.987654 0.99
4.5678 4.6
24.681357 25
76543 77000

Several sources suggest that this formula

=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))

will *round* a number to 2 significant digits, but I want to leave the
value unchanged and only *format* it.

 

[Jennifer Murphy]

Is there a way to *format* a number to N significant digits?

For N=2, the data on the left would get formatted as shown on the
right:

data display
0.0002468 0.00025
0.987654 0.99
4.5678 4.6
24.681357 25
76543 77000

Several sources suggest that this formula

=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))

will *round* a number to 2 significant digits, but I want to leave the
value unchanged and only *format* it.

Correction: It will round it to the number of digits in A2.

 

[Rick Rothstein]

For N=2, the data on the left would get formatted as shown on

the right:

data display
0.0002468 0.00025
0.987654 0.99
4.5678 4.6
24.681357 25
76543 77000

Several sources suggest that this formula

=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))

will *round* a number to 2 significant digits, but I want to
leave the value unchanged and only *format* it.

Here is a UDF (user defined function) that will handle normal numbers as
well as E-formatted number which you can use...

Function RoundSignificantFigures(Value As Variant, _
Significance As Long) As Double
Dim Num As String
Dim Parts() As String
Num = Format(Value, "0.##############################e+0;;0")
Parts = Split(CStr(Num), "E", , vbTextCompare)
If CDbl(Parts(0)) = 0 Then
RoundSignificantFigures = 0
Else
RoundSignificantFigures = CDbl(Format(Parts(0), "0" & Left(".", _
-(Significance <> 0)) & String(Significance _
- 1, "0")) & "E" & Parts(1))
End If
End Function

To install the function, press Alt+F11 to go into the VB editor, click
Insert/Module from its menu bar and then copy/paste the above code into the
code window that opened up. Return to the worksheet and you can use the UDF
just like a built-in Excel function. For example, write your value in A1 and
put this formula in some other cell...

=RoundSignificantFigures(A1,3)

This formula can be copied down or across if needed.
Is there a way to *format* a number to N significant digits?

Rick Rothstein (MVP - Excel)

 

[GS]

Jennifer Murphy laid this down on his screen :

Is there a way to *format* a number to N significant digits?

For N=2, the data on the left would get formatted as shown on the
right:

data display
0.0002468 0.00025
0.987654 0.99
4.5678 4.6
24.681357 25
76543 77000

Several sources suggest that this formula

=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))

will *round* a number to 2 significant digits, but I want to leave the
value unchanged and only *format* it.

Is there some reason why you can't set the format via the Format Cells
dialog?

 

[Rick Rothstein]

Is there some reason why you can't set the format via the

Format Cells dialog?

What format would to both of these?

data display
0.0002468 0.00025
76543 77000

Rick Rothstein (MVP - Excel)

 

[CWatters]

Is there a way to *format* a number to N significant digits?

For N=2, the data on the left would get formatted as shown on the
right:

data display
0.0002468 0.00025
0.987654 0.99
4.5678 4.6
24.681357 25
76543 77000

Several sources suggest that this formula

=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))

will *round* a number to 2 significant digits, but I want to leave the
value unchanged and only *format* it.

Couldn't google an answer but quite a few seem to have asked.

I think only way is to copy the data to new worksheet or column and then
ROUND it? That way you can still refer to the original in other
calculations.

or perhaps use scientific format if that's acceptable.

 

[joeu2004]

Is there a way to *format* a number to N significant digits?
For N=2, the data on the left would get formatted
as shown on the right:
data display
0.0002468 0.00025
0.987654 0.99
4.5678 4.6
24.681357 25
76543 77000
Several sources suggest that this formula
=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))
will *round* a number to 2 significant digits, but
I want to leave the value unchanged and only *format* it.

AFAIK, you cannot leave the value as-is and only change the format, unless
you are willing to use Scientific format. For example, 0.0E+00 will display
only 2 significant digits.

Otherwise, I believe you must use a helper cell that displays the number as
desired. In other formulas, you would reference the original cell if you
want the unchanged value.

I have not vetted the formula above. If you are amenable to using a helper
cell, please indicate that in a response, and we can vet the formula above
or offer a viable alternative.

 

[Jennifer Murphy]

Here is a UDF (user defined function) that will handle normal numbers as
well as E-formatted number which you can use...

Function RoundSignificantFigures(Value As Variant, _
Significance As Long) As Double
Dim Num As String
Dim Parts() As String
Num = Format(Value, "0.##############################e+0;;0")
Parts = Split(CStr(Num), "E", , vbTextCompare)
If CDbl(Parts(0)) = 0 Then
RoundSignificantFigures = 0
Else
RoundSignificantFigures = CDbl(Format(Parts(0), "0" & Left(".", _
-(Significance <> 0)) & String(Significance _
- 1, "0")) & "E" & Parts(1))
End If
End Function

To install the function, press Alt+F11 to go into the VB editor, click
Insert/Module from its menu bar and then copy/paste the above code into the
code window that opened up. Return to the worksheet and you can use the UDF
just like a built-in Excel function. For example, write your value in A1 and
put this formula in some other cell...

=RoundSignificantFigures(A1,3)

This formula can be copied down or across if needed.

Thanks for the UDF.

What does it do that the ROUND formula above doesn't do?

Why would I need it for scientific numbers? They are already rounded
to n digits, no?

 

[Jennifer Murphy]

Jennifer Murphy laid this down on his screen :

Is there some reason why you can't set the format via the Format Cells
dialog?

I'd love to. Show me the format string that will do all of the numbers
above.

 

[Jennifer Murphy]

Couldn't google an answer but quite a few seem to have asked.

I think only way is to copy the data to new worksheet or column and then
ROUND it? That way you can still refer to the original in other
calculations.

That's what the formula above does.

or perhaps use scientific format if that's acceptable.

Not a good choice for this application. I should have mentioned that.

 

[Jennifer Murphy]

AFAIK, you cannot leave the value as-is and only change the format, unless
you are willing to use Scientific format. For example, 0.0E+00 will display
only 2 significant digits.

Otherwise, I believe you must use a helper cell that displays the number as
desired. In other formulas, you would reference the original cell if you
want the unchanged value.

I have not vetted the formula above. If you are amenable to using a helper
cell, please indicate that in a response, and we can vet the formula above
or offer a viable alternative.

It looks like you are correct, so I will have no choice but to use
another cell (column). The formula above appears to work, based on
limited testing.

Thanks

 

[Rick Rothstein]

Thanks for the UDF.

You are welcome.

What does it do that the ROUND formula above doesn't do?

I have never seen that ROUND formula before, so I don't know if it works
correctly or not; whereas the code I posted was tested (back when I first
wrote it), so I know it works. Also, since it is a VB function, it can also
be called directly from any other VB code you write and used that way as
well.

Why would I need it for scientific numbers? They are already
rounded to n digits, no?

I’m not sure I understand what rounding your are referring to. Put this...

=1/11991

into a cell an tell me what rounding you would use to display it to 2
significant digits (remembering that this number could be mixed in with
normal numbers like 123.456)? My UDF let's you choose the number of
significant digits to display any number to without having to worry about
its size.

Rick Rothstein (MVP - Excel)

 

[GS]

GS presented the following explanation :

Jennifer Murphy laid this down on his screen :

Is there some reason why you can't set the format via the Format Cells
dialog?

Ok, I see the problem more clearly now. In this case I agree with the
already suggested use of a 'helper cell column' if you go with the
posted formula! Otherwise, Rick's UDT looks like a good alternative if
you expect scientific representation of values.

 

[Jennifer Murphy]

You are welcome.


I have never seen that ROUND formula before, so I don't know if it works
correctly or not; whereas the code I posted was tested (back when I first
wrote it), so I know it works. Also, since it is a VB function, it can also
be called directly from any other VB code you write and used that way as
well.

OK, but I could easily make the formula into a VB function, too.

I’m not sure I understand what rounding your are referring to.

If I use scientific formatting, such as "0.00E+00", the data will be
rounded to 3 significant digits -- one to the left of the decimal
point and 2 to the right.

Put this...

=1/11991

into a cell an tell me what rounding you would use to display it to 2
significant digits (remembering that this number could be mixed in with
normal numbers like 123.456)? My UDF let's you choose the number of
significant digits to display any number to without having to worry about
its size.

I think my first example is a good illustration for this, but to
reiterate, since the result is 0.000083395880243516, I would want it
formatted as "0.000083".

 

[Rick Rothstein]

I have never seen that ROUND formula before, so I don't know if it works

OK, but I could easily make the formula into a VB function, too.

As long as you know that formula works, sure; but if you are talking about
implementing it using VB functions, you might have to use caution with VB's
Round function... it uses Banker's Rounding when the number ends in 5 and is
being rounded to one less decimal place than the 5's position whereas
Excel's ROUND function does not.

If I use scientific formatting, such as "0.00E+00", the data will be
rounded to 3 significant digits -- one to the left of the decimal
point and 2 to the right.

I was thinking of the problem coming up with a custom format to handle any
number thrown at it... large whole numbers, floating point numbers less than
zero and scientific numbers.

I think my first example is a good illustration for this, but to
reiterate, since the result is 0.000083395880243516, I would want it
formatted as "0.000083".

How do you decide/control when to switch from floating point values to
scientific notation? Again, I am thinking of the general case where you
could have numbers of any possible size. By the way, I do not want you to
think I am "arguing" with you, rather, I'm just trying to explore the
general case where the numbers can vary in size.

Rick Rothstein (MVP - Excel)

 

[Jennifer Murphy]

As long as you know that formula works, sure; but if you are talking about
implementing it using VB functions, you might have to use caution with VB's
Round function... it uses Banker's Rounding when the number ends in 5 and is
being rounded to one less decimal place than the 5's position whereas
Excel's ROUND function does not.

That's a good point. I've been bitten more than once by a VB function
doing something different from the Excel function by the same name.

I was thinking of the problem coming up with a custom format to handle any
number thrown at it... large whole numbers, floating point numbers less than
zero and scientific numbers.

My numbers don't vary that much, but you're right. A general purpose
routine would have to handle anything.

How do you decide/control when to switch from floating point values to
scientific notation? Again, I am thinking of the general case where you
could have numbers of any possible size.

OK. I was only thinking of my numbers.

By the way, I do not want you to
think I am "arguing" with you, rather, I'm just trying to explore the
general case where the numbers can vary in size.

Of course. You have some good points. In any case, there's nothing
wrong with arguing when you have good arguments. ;-) I've always
thought that your contributions were very helpful.

 

[joeu2004]

I think my first example is a good illustration for this,
but to reiterate, since the result is 0.000083395880243516,
I would want it formatted as "0.000083".

Yes, I think all of your examples have been quite clear. Moreover, I think
it is clear that you want the result __formatted__ (displayed) with the
specified number of significant digits. You wrote: "I want to leave the
value unchanged and only *format* it".

You now realize that you must change the value (i.e. have a helper cell with
a formula that changes the value). But I suspect you are relying on the
behavior of the General format to display the specified number of
significant digits.

You can only take that so far. For example, if you wanted
0.000083395880243516 formatted to 6 significant digits in a cell with the
General format, formulas that return a numeric value rounded to 6
significant digits will display 8.33959E-05. I presume you want
0.0000833959.

I believe that requires a text result. The following user-defined function
should return text that formats the number to the specified significant
digits. You can format the cell with Horizontal Right Alignment.

If A1 contains the original value and A2 contains the number of significant
digits, then A3 might be the formula =vformat(A1,A2). If you want to use A3
as a number elsewhere, often it will work; e.g. =A3*2. But more generally,
use --A3. The double-negative (--) converts the numeric text to a number in
contexts where it would not otherwise be converted, e.g. IF(--A3>1,...)

The UDF....

Option Explicit

Function vformat(x As Double, sig As Long) As String
Dim s As String, p
If sig <= 0 Then sig = 1 _
Else If sig > 15 Then sig = 15
' create p(0 to 1) as Variant regardless of how
' Option Base is set
p = Split(Format(Abs(x), _
"." & String(sig, "0") & "E+00"), "E")
If p(1) <= 0 Then s = "0" _
Else s = Mid(p(0), 2, IIf(sig < p(1), sig, p(1)))
If p(1) > sig Then
s = s & String(p(1) - sig, "0")
ElseIf p(1) < sig Then
s = s & "."
If p(1) > 0 Then s = s & Right(p(0), sig - p(1)) _
Else s = s & String(-p(1), "0") & Mid(p(0), 2, 99)
End If
If x >= 0 Then vformat = s _
Else vformat = "-" & s
End Function

 

[Jennifer Murphy]

Yes, I think all of your examples have been quite clear. Moreover, I think
it is clear that you want the result __formatted__ (displayed) with the
specified number of significant digits. You wrote: "I want to leave the
value unchanged and only *format* it".

You now realize that you must change the value (i.e. have a helper cell with
a formula that changes the value). But I suspect you are relying on the
behavior of the General format to display the specified number of
significant digits.

You can only take that so far. For example, if you wanted
0.000083395880243516 formatted to 6 significant digits in a cell with the
General format, formulas that return a numeric value rounded to 6
significant digits will display 8.33959E-05. I presume you want
0.0000833959.

I believe that requires a text result. The following user-defined function
should return text that formats the number to the specified significant
digits. You can format the cell with Horizontal Right Alignment.

If A1 contains the original value and A2 contains the number of significant
digits, then A3 might be the formula =vformat(A1,A2). If you want to use A3
as a number elsewhere, often it will work; e.g. =A3*2. But more generally,
use --A3. The double-negative (--) converts the numeric text to a number in
contexts where it would not otherwise be converted, e.g. IF(--A3>1,...)

The UDF....

Option Explicit

Function vformat(x As Double, sig As Long) As String
Dim s As String, p
If sig <= 0 Then sig = 1 _
Else If sig > 15 Then sig = 15
' create p(0 to 1) as Variant regardless of how
' Option Base is set
p = Split(Format(Abs(x), _
"." & String(sig, "0") & "E+00"), "E")
If p(1) <= 0 Then s = "0" _
Else s = Mid(p(0), 2, IIf(sig < p(1), sig, p(1)))
If p(1) > sig Then
s = s & String(p(1) - sig, "0")
ElseIf p(1) < sig Then
s = s & "."
If p(1) > 0 Then s = s & Right(p(0), sig - p(1)) _
Else s = s & String(-p(1), "0") & Mid(p(0), 2, 99)
End If
If x >= 0 Then vformat = s _
Else vformat = "-" & s
End Function

Very creative, thanks.