How to calculate/properly display significant figures ending in 0

A

A Zaffiro

To report numbers in significant figures, Excel Help gives the following
formula and states that it is valid for numbers above zero:

=ROUND(B18,2-LEN(INT(B18))), where B18 is the number to round, and 2 is the
the number of significant figures desired.

In fact, it only works for numbers above 1, and if the number format is set
to 'general'

The formula works, but here's the problem: If the result ends in zero (e.g.
2.0), Excel only displays the last non-zero integer (e.g. 2). Since the last
zero is significant, this is a problem.
 
B

Bob Phillips

How about this

=TEXT(B18,"##0"&IF(2-INTO(B18)>0,"."&REPT("0",2-INT(B18)),""))

--

HTH

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

swatsp0p

If you format the cell with the formula as Number, you can set th
number of decimal places to display. If you set it to 1, your liste
formula will show 2.0 for a value of 2.015 (and will show 2000.0 for
value of 2015.15)

HTH

Bruc
 
A

A Zaffiro

Thanks. Inventive solution. That seems to work for numbers between 1 and
100. FYI: for numbers less than 1, I came up with:

=ROUND(B3,LEN(INT(1/B3))+1), where B3 is the decimal number to round, and
'1' is one less than the number of significant figures desired.

This formula suffers from the same zero problem. I will attemp to resolve
once I better understand the technique that you used to solve the zero
problem for numbers greater than 1.
 
A

A Zaffiro

Thanks Jerry. I used that plus some of Bob Phillip's ideas to create a
formula that solves the trailing zero display problem for 2 significant
figures and works for any number above zero including decimals. It gets
rather involved to write a general fomula that allows the number of sig fig's
to vary beyond 2. I'll share the formula after I have a chance to polish and
test thoroughly.

Alan Zaffiro
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top