Rounding/Significant figures

[cloots]

I'm trying to take a value in a given cell and round to the appropriate sig
figs as defined by a "user". I found a formula in here (Thanks to Jerry W.
Lewis, I believe) that will work for numbers below 1. However if the number
is one or greater, I would like it to report to the correct number of sig
figs and in scientific notation without insignificant trailing zero's.

Example:

453678 to 3 sig figs.
= 454000 or as I would like to see it 4.54 E5

I've tried converting it to a text and playing around with it that way,
based on some formulas I've found in here, but I'm not having much luck..

Any thoughts?

Thanks in advance!

 

[Bernd Plumhoff]

Hello,

Suggestion:
=TEXT(ROUND(A1,-3),"0.0#E+0")

HTH,
Bernd

 

[Bob Phillips]

I would just round it

=ROUND(A1,-3)

and add a custom format of 0.0#E+0 so as to keep it as a number

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[cloots]

Thanks all for the input... With your guys help and a coworkers input we've
come up with the following:

so if D10 contains the value and c10 contains the number of significant
figures needed. I took the following actions on the numbers from the top
down... This appears to fo the trick although I still need to incorporate
one "super formula" to do it all. Ohh boy the thrill of putting this in a
spreadhseet with a few hundred calculations

=TEXT($D10, "0.000000000000E+00") This converts it to scientific notation
=LEFT(E10,SEARCH("E",E10)-1) This gives the text in numeric form only
=ROUND(VALUE(F10),$C10-1) This rounds to the appropriate sig figs
=RIGHT(E10,LEN(E10)-SEARCH("E",E10)+1) This reports the magnitude of the
scientific notation
=TEXT(G10, "0." &REPT("0",$C10-1))&H10 This converts it back to text with
the appropriate sig figs (hopefully).

So far in my error checking it reports the correct number of sig figs and
removes the trailing zero's. I haven't given it exhaustive testing yet, now
to move it all together into one formula.

Anyt houghts or does anybody have any suggestions on a better idea? I'm
probably going overboard here, but I think this will do what I want.

 

[Jerry W. Lewis]

http://groups.google.com/group/microsoft.public.excel.programming/msg/2b244c8f41e91025
does not have any restriction on the number to be rounded.

Jerry

 

[cloots]

My apologies, you are correct, it does indeed round appropriatley. I needed
something to do a little more than that. Which turned out to be a lot bigger
pain than I hoped, but a guy in the office was able to write a macro that
solved the problem. Thanks everyone for the input!

Cloots