sig figs

[mandopolis]

can excel round to a certain number of sig figs w/o using the scientific function (i.e 8.47, 203, 42.5) all of those values have 3 sig figs, is there a function that will do this??? thanx for your help!!!

 

[Mark O'Brien]

I don't have an answer, but have a look at this thread:

http://www.mrexcel.com/board/viewtopic.php?topic=5521&forum=2

The last post may be useful.

 

[Jay Petrulis]

Hi,

This was posted yesterday to your other thread on this issue. Let us know if this will work for you.

With your raw data in A1 and the number of significant figures you desire in B1, try:

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

Bye,
Jay

 

[Mark O'Brien]

I didn't realise I referred you to yor own thread.

It was Jay's suggestion I was referring to though, so it's in this thread now.

 

[mandopolis]

the "<"text($C$24,0.000) did work for my previous question thatnx again!!

 

[mandopolis]

ok, jay's answer works, except for when theres an answer like 0.0468, i believe excel only recognizes numbers other than 0. i need an answer of .047. any help is always appreciated!! thanxs all

 

[Jay Petrulis]

Hi,

=ROUND(A1,B1-1-IF(INT(ABS(A1)),0,1)-INT(LOG10(ABS(A1))))

This will not automatically adjust as above for a number like 0.000468, though.

Bye,
Jay

 

[mandopolis]

jay, your first solution worked a little better for me. if i could only use a decimal format with the equation........

its a tough problem, but if you or anyone else can help, its always appreciated!!

marc

 

[Jay Petrulis]

Hi,

How did the first one work better? The amended version was exactly the same as the first if the integer part was not zero. This should have improved the situation somewhat.

Are you referring to something like 55.0468 returning 55 rather than 55.0? I don't know any way to work around that unless you format the cell, although you may be able to play with the TEXT function.

The problem with the second one returning 0.047 is that it is really only 2 significant figures, not 3.

Jay

 

[mandopolis]

ok, i got it, by using your 1st equation:

With your raw data in A1 and the number of significant figures you desire in B1=3, try:

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

and adding an additional if statement in another cell, that:

With your raw data in A1, the number of significant figures you desire in B2=2 and your previous cell in C3, try:

=IF(C3<.1,ROUND(A1,B2-1-INT(LOG10(ABS(A1)))),C3)

this takes care of the problem, when numbers are less than .1, and therefore solves the problem.

marc

really appreciate your help all these times guys!!

marc