Hi Niklas

Excel has no function for this (and I've always wondered why), but try this:

=ROUND(A1,2-INT(LOG(ABS(A1))))

The 2 is "significant digits minus 1", change to fit your needs or change it
to a cell variable.

HTH. best wishes Harald

"Niklas" skrev i melding
...
Hi
Are their any worksheet function I can use to convert a value to the
desiered significant values? At the moment I have created a UDF doing
this,
but I prefer to use worksheet function.

Some examples
Value Significant digits New value
-0.001 3 -0.001
1.99999999 3 2.00
2.00000001 3 2.00
0.012345 3 0.0123
0.00010 3 0.00010

Regards
/Niklas

Thank you. We can hope that Excel will have one in the future. I will use you
soloution and hopefully the users do not complain when they lose presicion
when 0.0010 will be converted to 0.001
Regards
/Niklas

"Harald Staff" wrote:

Hi Niklas

Excel has no function for this (and I've always wondered why), but try this:

=ROUND(A1,2-INT(LOG(ABS(A1))))

The 2 is "significant digits minus 1", change to fit your needs or change it
to a cell variable.

HTH. best wishes Harald

"Niklas" skrev i melding
...
Hi
Are their any worksheet function I can use to convert a value to the
desiered significant values? At the moment I have created a UDF doing
this,
but I prefer to use worksheet function.

Some examples
Value Significant digits New value
-0.001 3 -0.001
1.99999999 3 2.00
2.00000001 3 2.00
0.012345 3 0.0123
0.00010 3 0.00010

Regards
/Niklas

Just tell them that they don't. To you and me, 0.01 and 0.0100 mean
different things, to a computer it's the very same number.

Just by curiocity, what do you use this for ?

Best wishes Harald


"Niklas" skrev i melding
...
Thank you. We can hope that Excel will have one in the future. I will use
you
soloution and hopefully the users do not complain when they lose presicion
when 0.0010 will be converted to 0.001

I teach High School physics, and I want to put together a series of self
quizzes for my students. At this level, there are only a handful of
different ways you can ask questions, so all that really changes is the
numbers. Many of my students just need the practice with their calculators
more than anything else.

Ideally, I'd like the quiz to generate the answers (easy) to the correct
number of significant digits (a little more complicated) without having to
cheat (that is, always having the same number of sig digs).


"Harald Staff" wrote:

Just tell them that they don't. To you and me, 0.01 and 0.0100 mean
different things, to a computer it's the very same number.

Just by curiocity, what do you use this for ?

Best wishes Harald


"Niklas" skrev i melding
...
Thank you. We can hope that Excel will have one in the future. I will use
you
soloution and hopefully the users do not complain when they lose presicion
when 0.0010 will be converted to 0.001

I know this post has been here awhile, but I just came across it and thought
I should comment.

This is a great function for finding the significant figures. However, this
formula needs some modification and a snippet of code if it is to be used in
VBA. This is due to the fact that in VBA log() and ln() are one in the same
and considered to be the natural log. Here's I used this function in my code:

....
For Each c In Range_B
c.Value = Round(c.Value, 5 - Int(Log10(c.Value)))
Next

And add the following:

Static Function Log10(X)
Log10 = Log(X) / Log(10#)
End Function


Note: In this example I was using 5 sig figs.

Hope this is helpful
Mike

"Harald Staff" wrote:

Hi Niklas

Excel has no function for this (and I've always wondered why), but try this:

=ROUND(A1,2-INT(LOG(ABS(A1))))

The 2 is "significant digits minus 1", change to fit your needs or change it
to a cell variable.

HTH. best wishes Harald

"Niklas" skrev i melding
...
Hi
Are their any worksheet function I can use to convert a value to the
desiered significant values? At the moment I have created a UDF doing
this,
but I prefer to use worksheet function.

Some examples
Value Significant digits New value
-0.001 3 -0.001
1.99999999 3 2.00
2.00000001 3 2.00
0.012345 3 0.0123
0.00010 3 0.00010

Regards
/Niklas