significant digits

[Gordon]

Is there a format available to display significant digits? If you have the
following data:
0.99
1.0
1.1
1.11
is there a format you can use to display say 2 significant digits for the
entire data set? Two significant digit display would be:
1.0
1.0
1.1
1.1

Is there a formula that will use only X significant digits in the calculation?

We are required to report to regulatory agencies using significant digits
rather than decimal places.
Thanks!

 

[Spiky]

How are your significant digits defined? I would just translate that
into decimals, the example you gave is 1 decimal place. So you can use
ROUND for a formula or format cells to "0.0".

 

[Gordon]

Significant digits are different from decimal places, in that you are only
looking at a set number of significant placeholders rather than looking at a
set decimal point.
..1 .10
1 1.0
12 12
123 120
1234 1200
etc.

The problem with the ROUND function is that it always rounds either up or
down. Again, our rules are different. We must round even up, odd down or
odd up even down.
This reduces bias from the rounding process.

 

[Spiky]

You can use IF combined with ROUNDUP/DOWN and probably ISEVEN/ISODD
for the formulas.

I do know what "significant digits" means. What I asked is, "How are
YOUR significant digits defined". If you have a set method of
selecting the significant digits, like typing a "2" into a cell,
perhaps you can find a method of instructing Excel what to do.

There are also places to find help via Google. Ex:
http://www.vertex42.com/ExcelTips/significant-figures.html

But I have to ask....you find more bias based on >=5 than on even/odd
rounding? I'd like to see the math on that.

 

[Gordon]

Spiky,
We use Excel to generate regulatory reports. The digits in the reports must
be to 2 significant digits. The problem is in the formatting. No matter
what you enter, what you see reverts back to the formatting. So if you enter
1.0 and the formatting is set to .00, you will see 1.00. The agency does not
like that. I could change the formatting every time, for each data point,
but that is asking for an error. Is there something automatic that will work
with the formatting?

It looks like the second (huge!) formula on the webpage you pointed me to
will work, but I couldn’t figure out the inputs. Can you give me the 5th
grader explanation?


Rounding… I wish we could just use the <=5 standard rounding. I fought it
and lost. When all is said and done, you must satisfy the boss.

Thanks for your time and help!

 

[Spiky]

It looks like the second (huge!) formula on the webpage you pointed me to
will work, but I couldn’t figure out the inputs. Can you give me the 5th
grader explanation?

You should be able to change all the instances of "value" to reference
your number. So whatever cell that is.

And change all of the "sigfigs" to reference how many digits are
needed. If it is always 2, just change these to "2" (with no quotes).
If it might change, I'd put the "2" at the top of your page in a cell
and reference that cell in the big formula.

Looks like 4 instances of each. Also, it probably won't copy very
well. I tried it out and it pasted into 3 cells, so you have to cut
and paste to get it into one formula. But it appears to work just
fine.

If I could remember all the LOG stuff I once knew, I still don't think
that could be explained at a 5th grade level. ;-)

Oh, this returns Text. If you want to actually use these numbers in a
later calculation, wrap a VALUE around it. Just add "VALUE(" at the
beginning and one more parenthesis at the end.

 

[Bernd P]

Hello Gordon,

=--TEXT(A1,"0.0E+0")

Note that 0.99 presented with 2 significant digits still is 0.99.
Leading zeros dont count.

Regards,
Bernd

 

[Gordon]

Excellent, Bernd P! A simple and elegant solution!
Spiky, thanks again for your help.

 

[Gordon]

Bernd P, if I enter 1.0 or 1.00, it only shows as 1, which to the regulators
is just as wrong as 1.00.

 

[Ron Rosenfeld]

Is there a format available to display significant digits? If you have the
following data:
0.99
1.0
1.1
1.11
is there a format you can use to display say 2 significant digits for the
entire data set? Two significant digit display would be:
1.0
1.0
1.1
1.1

Is there a formula that will use only X significant digits in the calculation?

We are required to report to regulatory agencies using significant digits
rather than decimal places.
Thanks!

What are you using for a definition of "significant digits"?

I ask because your first example shows 0.99 --> 1.0 and the definitions of
which I am aware only count zeros as significant if they occur between two
non-zero digits; or if they are trailing zeros in a number with a decimal
point.
--ron

 

[Gordon]

Ron,
You are correct. 0.99 is two significant digits. My mistake.

 

[Ron Rosenfeld]

Ron,
You are correct. 0.99 is two significant digits. My mistake.

"Ron Rosenfeld" wrote:

Well, you are going to have to return a string in order to retain the required
trailing zero's.

Here is a User Defined Function that will do that.

To enter this, <alt-F11> opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this, enter the formula =RSD(cell_ref, numSigDigits) into some cell.

e.g. =RSD(A1,2) for 2 significant digits.

==============================
Option Explicit
Function RSD(n As Double, SigDigits As Integer) As String
'outputs a string right padded with zeros to SigDigits
Dim fmt As String
Dim Sign As Integer
Dim ZerosLeft As Integer, ZerosRight As Integer, ExtraZeros As Integer

Sign = Sgn(n)
n = Abs(n)

RSD = Application.WorksheetFunction.Round _
(n, Fix(-Log(n) / Log(10)) + SigDigits - 1)
Debug.Print n, RSD

ZerosLeft = InStr(RSD, ".") - 1
If ZerosLeft = -1 Then ZerosLeft = Len(RSD)
ZerosRight = Len(RSD) - ZerosLeft - 1
If Fix(RSD) = 0 Then
ZerosLeft = 0
fmt = "0"
End If

ExtraZeros = SigDigits - (ZerosLeft + ZerosRight)
fmt = fmt & WorksheetFunction.Rept("0", ZerosLeft)
If (ZerosRight + ExtraZeros > 0) Then
fmt = fmt & "."
fmt = fmt & WorksheetFunction.Rept("0", ZerosRight + ExtraZeros)
End If

RSD = Format(Val(Sign * RSD), fmt)

End Function
=========================
--ron

 

[Ron Rosenfeld]

Ron,
You are correct. 0.99 is two significant digits. My mistake.

The text formula on the page that Spiky referred you two might be more
convenient. I just noted that and it seems to work properly, also.
--ron

 

[Bernd P]

Hello Gordon,

Then wrap another TEXT function around it:
=TEXT(--TEXT(A1,"0.0E+0"),"0.00")
Or format the cell accordingly.

Regards,
Bernd

 

[Gordon]

Bernd P's formula seems to work well:
=TEXT(--TEXT(A1,"0.0E+0"),"0.00")

It hasn't failed me yet.

Thanks to all, what a great resource!

 

[Bernd P]

Hi Ron,

Your UDF returns for RSD(0.99,2)=1.0

My UDF Dbl2NSig results in 0.99:
http://www.sulprobil.com/html/dbl2nsig.html

Regards,
Bernd

 

[Harlan Grove]

Then wrap another TEXT function around it:
=TEXT(--TEXT(A1,"0.0E+0"),"0.00")
Or format the cell accordingly.

FWIW, this could mishandle numbers between 0 and 0.1, e.g., 0.093
should remain 0.093 rather than becoming 0.1. If the value were x and
the number of significant digits n, to be exhaustive you'd need
something like

=IF(x<0,"-","")&TEXT(--TEXT(ABS(x),"."&REPT(0,n)&"E+0"),
"."&REPT(0,MAX(0,IF(ABS(x)<1,1,n-1)-INT(LOG10(ABS(x))))))

 

[Ron Rosenfeld]

Bernd P's formula seems to work well:
=TEXT(--TEXT(A1,"0.0E+0"),"0.00")

It hasn't failed me yet.

Thanks to all, what a great resource!

It doesn't seem to work properly here.

1.234 --> 1.20
Two sig digits should be 1.2
Three sig digits should be 1.23
--ron

 

[Ron Rosenfeld]

Hi Ron,

Your UDF returns for RSD(0.99,2)=1.0

My UDF Dbl2NSig results in 0.99:
http://www.sulprobil.com/html/dbl2nsig.html

Regards,
Bernd

Your correct. I posted in error.

WRT your UDF, why not modify it so it also handles negative numbers?

=dbl2nsig(-0.99,3) --> "0.-.9"
--ron

 

[Bernd P]

Hello Harlan,

Right.

--TEXT(A1,"0.0E+0") calculates the 2 most significant digits, and the
rest was IMHO presentational guess work.

Regards,
Bernd