Significant Figures

Barry,

From the "For what it's worth" department, brace yourself, here comes the slightly reduced formula...

=IFERROR(IF(LOG10(B)<1, LEFT(MROUND(B,10^(INT(LOG10(B))-2))&"00", 4-INT(LOG10(B))), IF(LOG10(B)<2,LEFT(MROUND(B,10^(INT(LOG10(B))-2))&".0", 4),MROUND(B,10^(INT(LOG10(B))-2)))&""), "")

I noticed a flaw affecting very small values in the more bulbous expression, so I decided to take another run at it. Here's a shot.



Jerry

septimus ii wrote:
While I appreciate (and am rather impressed by) the ingenuity of the solutions posted here, it's not quite what I originally meant.

I learnt that there are two ways of rounding numbers - to a specific number of decimal places or a specific number of significant figures. While the former is easy to adjust in the inspector and other places, I can't find any easy ways of displaying the latter. I'm not sure if this is a small gap in the program, or merely an option which I have missed.

It's a "small gap in the program," for which the responders have attempted (successfully, I think) to provide some chinking.

It also looks like an opportunity to Provide Numbers Feedback making a feature enhancement request for future editions of Numbers. Use the item of that name in the Numbers menu, or the link in the previous sentence.

Regards,
Barry

septimus ii wrote:
I can easily find several options relating to the number of decimal places shown for a number in Numbers, but I can't find any way of setting a specific number of significant figures instead - something which is often more useful if your values are over a wide range. Is there any way of doing this?

There's always Scientific Notation. It's my impression that displaying a fixed number of significant digits is basically what it is for, at least that's what it offers over Engineering notation, which is my preference. (Engineering notation isn't offered in Numbers.)

Jerry

Jerrold Green1 wrote:
Don't fall over laughing until you try this:

Too late! 🙂

Don't fall over laughing until you try this:

=IFERROR(IF(LEFT(MROUND(B2,10^(INT(LOG10(B2))-2)), 2)="0.", MROUND(B2,10^(INT(LOG10(B2))-2))&RIGHT("000", 5-LEN(MROUND(B2,10^(INT(LOG10(B2))-2)))), IF(MROUND(B2,10^(INT(LOG10(B2))-2))<100, MROUND(B2,10^(INT(LOG10(B2))-2))&RIGHT("000", 4-LEN(MROUND(B2,10^(INT(LOG10(B2))-2)))),LEFT(MROUND(B2,10^(INT(LOG10(B2))-2)), 3)))&"", "")

It may not be worth it, but it works. I'm not saying it couldn't be streamlined 🙂

The result is a string, but should work if you are primarily interested in the look of it.

Jerry

Message was edited by: Jerrold Green1

I just wished to add that,
+if I understand well the OP's question+,
he wanted to get a *_fixed number of figures_* so the asked formula would return

0.000000000124
00.00000000012
000.0000000001
00000000000124

It's what is printed by some entities when printing checks.

I wrote a slightly modified formula to give the described behaviour.



And doing that, I discovered that there is a problem with the original formula.

In rows 3 & 4 is the modified formula
In row 5 is the original Jerrold's one.

I dislike to point an error when I don't bring a soluce but I have no time available to rework the formula.

Yvan KOENIG (VALLAURIS, France) mardi 18 mai 2010 16:14:25

Yvan,

I don't believe that you are interpreting the question correctly. The OP's second post gives an example of what he wishes to see. Badunit's expression, a very clever and efficient one, works except if his result rounds to one or more zeros, in which case it returns fewer than the requested number of digits.

Notice that the OP's example uses a column title of "Frequency". With frequency generating equipment it is common to provide three dials to set the frequency, and another selector to set the decade, or multiplier.* With the result that the OP seeks, three displayed digits, beginning with the first non-zero digit, would be transferred to the frequency generator dials.

Jerry

*I should say it "once" was common to have three dials. In the digital age the number of dials seems unlimited.

Message was edited by: Jerrold Green1

You did not miss anything. There is no function I am aware of that rounds to a specified number of significant digits, only a specified number of decimal places. I am not aware of a cell format that will do it either. We were trying to develop a solution to the problem but, while the solutions are academically interesting, they are kludgy.

s,

There are very few cases requiring a fixed number of significant digits in a display, so I believe that you will find that the "gap" is quite universal. If you Google significant digits you will find discussions on determining/calculating the proper number of digits to display, but few references describing how to automatically produce that number of places in the display.

Most users will find that Scientific Notation in combination with control of the number of places after the decimal is satisfactory.

I learnt that there are two ways of rounding numbers - to a specific number of decimal places or _a specific number of significant figures_

This is what badunit gave you in his original response, and the method was as easy as you are likely to find.

Jerry

There may be a better way to do this but I think the following formula will round the number to the specified three digits:

=MROUND(B2,10^(INT(LOG10(B2))-2))

Badunit,

That's a very good approach. It converted all but the 0.790 correctly, and would have the same problem with any of the case where the trailing digits round to zero.

Jerry

Oops

I missed the second message by the OP.

Yvan KOENIG (VALLAURIS, France) mardi 18 mai 2010 17:04:44