Significant figure code - 3 sig figs

[chromachem]

We looked all over for code in the public domain that would format a cell to 3 significant figures. We finally wrote our own code. It is not elegant, but it gets the job done. If this code is any help to anyone, please feel free.

Please Login or Register  to view this content.

If anyone would like to review/improve upon the code, we would be glad to see what you come up with.

Thanks to everyone that has helped us in the past.

[NBVC]

Is this accidentally marked as Solved?

[shg]

If you just want a rountine that rounds to three significant digits,

Please Login or Register  to view this content.

To demonstrate, in A1, =RAND() * 10^RANDBETWEEN(0,5)

In B1, =SigRnd(A1, 3) and format as General.

[chromachem]

A couple of things

1) It seems for this function to work the cells need to be formatted to general. If the format is numerical, the number is rounded. If the cell is formatted to more places than the function result zeros show up.

2) I wrote the following macro to test the function. If 7.999 is in the active cell the function will output 8. We need to display 8.00. For our purposes, the zeroes are significant.

Nevertheless, your code is very slick. I'm just wondering if there is an easy way to have the results formatted as follows:

0.XXX
X.XX
XX.X
XXX
XXX0
XXX00
XXXX00

Please Login or Register  to view this content.

[shg]

It just rounds the result to three significant digits. If you want a complementary display format to show all and just those digits, you'd need different code.

Or you could format the cells as 0.00E+0, which would always show three significant digits, without of any rounding function, and preserve the underlying value.

[sweep]

As an addendum for anyone interested, this formula will round A1 to A2 significant figures.

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

[chromachem]

A big thanks to SHG and everyone that responded to my post. I went back and took a look at the code SHG posted. The code does do exactly what I was trying to accomplish.

Thanks again to all!!

[Gunther Maplethorpe]

It just rounds the result to three significant digits.

Not exactly. A "significant digit" is any of the digits of a number beginning with the digit farthest to the left that is not zero and ending with the last digit farthest to the right that is either not zero or that is a zero but is considered to be exact. We in hte scientific community typically will record digits - including zeroes - out as far as our instrument will read precisely.

Examples:
123.456 has six significant digits.
123.45600 has eight significant digits.
0.200 has three significant digits
0.002 has one significant digit.
00123.45600 has eight significant digits.
201 has three significant digits.
200 is ambiguous; it may have one or three significant digits.

As such, this code does not, precisely, round to 3 significant digits.

=ROUND(A1,A2-1-INT(LOG10(ABS(A1)))) is similar; for example, if given 5.000000 in A1 and 3 in A2, it will result in 5 - it should be 5.00.

[shg]

Not exactly.

Umm, yes, exactly.

It returns a value with the specified number of significant digits, as a number. How you format it is up to you; scientific format is simplest if it's made clear that all digits shown are significant.

The value could be returned in string format, but at the expense of complicating downstream calculations, and in any case, you couldn't eliminate the ambiguity of significant digits of numbers lacking a decimal point; the overbar notation is not available to strings without some Unicode razzle-dazzle.

[Gunther Maplethorpe]

What I was trying to point out (and I obviously failed) is that there's some semantic confusion here. With "significant digits", the formatting is integral to the meaning. Teh specified number of digits is not the same as the significance of the digits. "5" is not the same as "5.00", when we're talking about significance....and that, I believe, is the root of this problem.

I came across this thread when I was looking for a solution similar to that of the OP; I'd like something that will return significant digits in this specific, scientific sense of the phrase, where 5 <> 5.00. Though the solutions here come close, there's a little bit missing.

I, for one, would like something that did the following...I'm not sure it's possible, though:

10/3 => 3
10/3.0 => 3.3
10.00/3.000 => 3.333

In these cases, the return result has the same number of significant digits as the operand with the least number of sigdigs.

The OP - at least, as I interpret it - would like to keep trailing zeroes when they are significant. That is, "5.0012" rounded to three sigdigs should return "5.00", not "5". When I add 2.501 to 2.50, I'd like a result of 5.00 to display - but I'd also like 0.002501 added to 0.00250 to display as 0.00500.

Again, I'm not sure this is possible without resorting to scientific notation.

[shg]

The rounding of a number to some significant number of digits is a mathematical operation, not a representational one; they may be complementary, but they are separate and distinct.

I, for one, would like something that did the following:

10/3.0 => 3.3

How would you do that without knowing the number of significant digits in "10"?

Though the solutions here come close, there's a little bit missing. ... The OP - at least, as I interpret it - would like to keep trailing zeroes when they are significant.

There may be some level of transference in that statement.

The code does do exactly what I was trying to accomplish.