Significant Figures Function

Thanks for sharing!

Re: Significant Figures Function

Hi Jon

This is brilliant. Thank you.

Stephen

Re: Significant Figures Function

Jon,

Pretty slick method.

Now next question. Can you make it so that if I enter two numbers that it will perform some indicated math and internally figure out the answer to the correct significant number of digits.

=sigFig(strArith,num1,num2)

Now I realize that you may have to indicate if one number was an exact number. I ask because if you use multiple calls then it would be easy to create make formula to perform calculations for say a chemistry class that always have multiple steps but sig figs are extremely important.

Regards,
Barry

Re: Significant Figures Function

Hi Jon

It seems this doesn't work with some numbers. eg show 1 to 2sf should display 1.0, but shows 1 instead. Only get 1.0 if you format the no. of dps, which then prevents the correct sf appearing.
I didn't test this, but Steve Aprahamian did, and, as per usual, he got the finer detail that lesser mortals like me missed! Apart from that shortcoming, it works really well.
Cheers
Stephen

Re: Significant Figures Function

Nice work Jon.

The display isn't right... but the value seems to be fine.

Most will be content just having something to give correct values for computations.

Good point though Kiwi.

Re: Significant Figures

Steve Aprahamian suggested using =TEXT(....,"0.000"), etc to get it looking right, then using =VALUE(cell...) to handle it as a number. Sounds ok, so long as we can make the correct no. of zeros appear in the TEXT formula. Make sense?
KiwiSteve

Re: Significant Figures Function

Hi Jon

You can also use +0 at the end of any formula that returns a number as text, or precede the formula with;
=-- as apposed to only =

Re: Significant Figures

To fix the cell showing 1.0 instead of just 1:

Conditionally format your cells so that when =len(a1)=1 changes your formatting to Number w/ 1 decimal point (or however many sig figs you want)

Re: Significant Figures Function

This is a great solution for significant figures using Excel formulas, however there are a few numbers that do "break" it. Here they are:

99.95 - 99.999999999999999
9.995 - 9.999999999999999
0.9995 - 0.999999999999999
0.09995 - 0.099999999999999
etc., etc. all the way down until we reach Excel's limit for storing decimal places.
and finally, Zero. Zero results in a #NUM error due to the LOG10 function.

One solution is to manually add an exception for each of these cases using if statements.
Other than that, this is a pretty bulletproof formula and I see no issue with using the VALUE function to perform additional calculations on these sig'd numbers.

Cheers!

Re: Significant Figures Function

In the above post, 99.95 - 99.999999999999999 represents a range of numbers. It should be read as "every possible number Excel can store between and including 99.95 to 99.999999999999999". Or if you'd prefer it in an Excel formula format: =AND(value>=99.95,value<=99.999999999999999)

Dont forget the negatives: =AND(value>=-99.999999999999999,value<=-99.95)

Cheers!