Formatting for Significant Figures

[Victor Eldridge]

Howdy All ,

Can anybody tell me how I format cells to display
only a certain amount of Significant Figures ?
For example , the column on the left is the numbers
I have , and the the column on the right is how I need
them to be displayed (in this case - 2 sig figs)

0.0860 0.086
0.1760 0.18
0.3200 0.32
1.14 1.1
1.160 1.2
48.365 48
36954 37000

If it can't be done with formatting , is there formula
that could do it ?
Any help would be very much appreciated…

v...@ultratrace.com.au

[Harald Staff]

Hi Victor

There may be easier ways or shorter formulas, but this do return 2
significant digits from A1:
=INT(A1*10^(1-ROUND(LOG10(A1),0)))/10^(1-ROUND(LOG10(A1),0))
For 3 digits, replace 1 in (1-ROUND... with 2. For 4, replace with 3.

Best wishes Harald

[Victor Eldridge]

Thanks Harald - that's got me on the right track I think.

I see you actually listened to your maths teacher back in high school.
I wish I'd done the same... :-)

v...@ultratrace.com.au

[Jerry W. Lewis]

What about

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

to round A1 to A2 significant figures?

Jerry W. Lewis
Stat...@erols.com

[Bernard Liengme]

Hello Victor,
This user defined function will do it for you:
Function TwoSig(num)
Dim mantissa, power
power = Int(Application.Log(num))
mantissa = num / (10 ^ power)
mantissa = Application.Round(mantissa, 1)
TwoSig = mantissa * 10 ^ power
End Function

I did not have time to test it throughly but it works with all
the numbers you gave and a few more that I tested it with.
Good luck
Bernard

[Harald Staff]

Jerry W. Lewis wrote:
>
> What about
>
> =ROUND(A1, A2 - 1 - INT(LOG10(ABS(A1))))
>
> to round A1 to A2 significant figures?

Very good, Jerry.
Harald :-)

[Leo Heuser]

It's a beauty, Jerry!

Jerry W. Lewis skrev i meddelelsen <36079BDF...@erols.com>...

>What about
>
> =ROUND(A1, A2 - 1 - INT(LOG10(ABS(A1))))
>
>to round A1 to A2 significant figures?
>

>Jerry W. Lewis
>Stat...@erols.com
>

[Victor Eldridge]

A giant thank you to all who helped.

And yes , Jerry's formula is a classic !