# Significant Figures

• HELP Once again I am driving myself insane.
We need to format cells in our worksheet to show significant figures.

We need to post lab results using the correct number of significant figures.

Ex.

0.1 must be displayed as 0.100

10 needs to be displayed as 10.0

105 as 105
1023 as 1020
10343 as 10300
165435 as 165000

I have written a complicated if then else statement for every cell. This statement is in the cell, not vbscript. Selection.numberformat statement will only work using vbscript.

The statment works perfectly EXCEPT when a number is an exact number.

The problem is that when some of the trailing digits are 0's they get truncated.

We always want numbers less than 1 to show 3 decimal places
Numbers between 1 and less than 10 should show 2 decimal places.
Numbers between 10 and less than 100 should show 1 decimal place.

My statement works fine for numbers 100 and above.
My statement also works fine for numbers less than 100 when there are no trailing zeroes.

10.2 shows as 10.2 but 10.0 shows as 10

Please - if you are familiar with significant figures and know of a solution, I would be greatly appreciative of any help

• what i do

Here is what I do for significant digits. the code is setup for your question.

Quote

We always want numbers less than 1 to show 3 decimal places
Numbers between 1 and less than 10 should show 2 decimal places.
Numbers between 10 and less than 100 should show 1 decimal place.

Code
``````if cell.value < 1 then
cell.NumberFormat = "0.000"
elseif cell.value < 10 then
cell.NumberFormat = "0.00"
elseif cell.value < 100 then
cell.NumberFormat = "0.0"
endif``````
• Sig figs

Hi Jong,

My if statement is in a cell. Not vb script.
The tech enters data in 1 cell, and the other cell needs to show the correct number of sig figs.

Thanks,

BB

• Hi, I am having the same exact issue as the OP. I figured out a code to spit out the correct number of sig figs in every cell when running the macro, my issue is with numbers below 100, where the rounding is effective but the display numbers are not rounded to 3 sig figs effectively.

Curious if anyone has figured out a solution to this issue?

• Hi mdeavila,