Significant figures & decimal points

[Markdon]

Hi, been browsing the forum and thought i'd cracked my problem, alas not quite, and im struggling now to continue with my project.

Basically, im trying to create a spreadsheet where when i enter a lab result, it does what it needs to do, and the end result is a number corrected to two sig figs, which i got from the forum:
=ROUND(EC67,2-(1+INT(LOG10(ABS(EC67)))))

The spreadsheet is already made, i have been using it for years, but i always have to go back in to correct the sig figs, so figured i'd have a go at automating it.So i have copied my results table, and inserted the above formula into each cell, and it works, sort of.I want numbers less than 10, to be displayed like 9.9, 0.0025, and numbers 10 or above to be whole numbers. The decimal points are all over the place, and im getting numbers like 200.00, 0.9, 2.10, 3.20, 0.40

The problem here is every cell has the chance to be any number from 0.000000001 to 500, so i need a formula that is variable in this way? Something i can add in if it is greater than 9.9 to remove the decimal point?

Thanks for any help.Mark.

 

[BrianMH]

Does it matter if the cell has the full value and just displays the way you want or does the value actually need changing?

 

[Markdon]

The cells will be mirrored from the original results, so i have full results, then corrected reults in a table under it.

So for the corrected table, i just want the results expressed as in the original post, thanks for looking.

Mark.

 

[Markdon]

The below are examples of cells that have had the formula inserted into them, not sure if the cells need the formatting reset before the formula is added or something?

0.00015

0.00044​

0.00110​

0.00039​

0.00073​

0.03100​

0.00810​

0.00083​

0.00042​

0.00130​

0.00130​

0.00003​

0.00003​

0.00044​

0.04600​

0.00018​

<TBODY>
</TBODY>

 

[Markdon]

Another example of a small section of the table:

I'm thinking i need something relating to decimal points to fix this?

<TBODY>
[TD="width: 28%, colspan: 2"]

Antimony​

​

​

​

​

​

[/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

0.56​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

0.42​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

0.98​

​

​

​

​

​

[/TD]

[TD="width: 15%"]

Arsenic​

​

​

​

​

​

[/TD]
[TD="width: 13%"][/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

1.70​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

0.15​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

1.9​

​

​

​

​

​

[/TD]

[TD="width: 28%, colspan: 2"]

Chromium​

​

​

​

​

​

[/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

4.10​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

5.80​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

9.9​

​

​

​

​

​

[/TD]

[TD="width: 15%"]

Cobalt​

​

​

​

​

​

[/TD]
[TD="width: 13%"][/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

1.50​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

0.17​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

1.7​

​

​

​

​

​

[/TD]

[TD="width: 15%"]

Copper​

​

​

​

​

​

[/TD]
[TD="width: 13%"][/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

2.80​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

5.50​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

8.3​

​

​

​

​

​

[/TD]

[TD="width: 15%"]

Lead​

​

​

​

​

​

[/TD]
[TD="width: 13%"][/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

120​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

2.60​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

120​

​

​

​

​

​

[/TD]

[TD="width: 28%, colspan: 2"]

Manganese​

​

​

​

​

​

[/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

31.00​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

2.30​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

33​

​

​

​

​

​

[/TD]

[TD="width: 15%"]

Nickel​

​

​

​

​

​

[/TD]
[TD="width: 13%"][/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

3.20​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

3.20​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

6.4​

​

​

​

​

​

[/TD]

[TD="width: 28%, colspan: 2"]

Thallium​

​

​

​

​

​

[/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

1.60​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

0.21​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

1.8​

​

​

​

​

​

[/TD]

[TD="width: 15%"]

Tin​

​

​

​

​

​

[/TD]
[TD="width: 13%"][/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

5.00​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

0.20​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

5.2​

​

​

​

​

​

[/TD]

[TD="width: 28%, colspan: 2"]

Vanadium​

​

​

​

​

​

[/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

4.90​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

0.60​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

5.5​

​

​

​

​

​

[/TD]

[TD="width: 15%"]

Mercury​

​

​

​

​

​

[/TD]
[TD="width: 13%"][/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

0.10​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

0.04​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

0.14​

​

​

​

​

​

[/TD]

[TD="width: 28%, colspan: 2"]

Cadmium​

​

​

​

​

​

[/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

0.10​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

0.14​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

0.24​

​

​

​

​

​

[/TD]

[TD="width: 47%, colspan: 3"]

Cadmium & Thallium Combined​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

1.7​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

0.35​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

2.0​

​

​

​

​

​

[/TD]

[TD="width: 47%, colspan: 3"]

Total Group III Metals​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

170​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

21​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

200​

​

​

​

​

​

[/TD]

[TD="width: 28%, colspan: 2"]

Beryllium​

​

​

​

​

​

[/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

0.70​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

0.21​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

0.91​

​

​

​

​

​

[/TD]

[TD="width: 28%, colspan: 2"]

Selenium​

​

​

​

​

​

[/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

0.40​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

0.28​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

0.68​

​

​

​

​

​

[/TD]

[TD="width: 15%"]

Zinc​

​

​

​

​

​

[/TD]
[TD="width: 13%"][/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

0.40​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

0.89​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

1.30​

​

​

​

​

​

[/TD]

[TD="width: 28%, colspan: 2"]

Molybdenum​

​

​

​

​

​

[/TD]
[TD="width: 20%"][/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 15%"]

0.40​

​

​

​

​

​

[/TD]
[TD="width: 2%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 16%"]

0.21​

​

​

​

​

​

[/TD]
[TD="width: 3%"]

<​

​

​

​

​

​

[/TD]
[TD="width: 13%"]

0.61​

​

​

​

​

​

[/TD]

</TBODY>

 

[Markdon]

could i use conditional formatting to do this?

 

[shg]

Maybe ...

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

0.000003414993​

[/td][td]0.0000034[/td][/tr]

[tr][td]

2​

[/td][td]

0.000004546042​

[/td][td]0.0000045[/td][/tr]

[tr][td]

3​

[/td][td]

0.000017288969​

[/td][td]0.000017[/td][/tr]

[tr][td]

4​

[/td][td]

0.000374432682​

[/td][td]0.00037[/td][/tr]

[tr][td]

5​

[/td][td]

0.002497138034​

[/td][td]0.0025[/td][/tr]

[tr][td]

6​

[/td][td]

0.031210539936​

[/td][td]0.031[/td][/tr]

[tr][td]

7​

[/td][td]

0.673720203234​

[/td][td]0.67[/td][/tr]

[tr][td]

8​

[/td][td]

1.115940829872​

[/td][td]1.1[/td][/tr]

[tr][td]

9​

[/td][td]

11.115181186071​

[/td][td]11.[/td][/tr]

[tr][td]

10​

[/td][td]

288.169494750620​

[/td][td]290[/td][/tr]
[/table]

The formula in B1 and copied down is

=TEXT(ROUND(A1, 2-1-INT(LOG(ABS(A1)))), 0 & IF(2-INT(LOG(ABS(A1)))>0, "." & REPT(0, 2-1-INT(LOG(ABS(A1)))), ""))

 

[Markdon]

Answered my own question, conditional formatting works, set the range to:

10 to 100, 0 decimal places
1 to 9.9, 1 decimal place
0.1 to 0.99, 2 decimal places
etc etc
also added cell equal to 0.00000000000000(absolute zero value) 1 decimal place

phew. just got to add it to all my sheets.

 

[Markdon]

Sorry shg, hadnt refreshed the page when i wrote my reply!

Thats some long formula you have there! may use that if it works, trust a formula more than conditional formatting i think??

 

[BrianMH]

You can use custom formatting try the number format [>=10]0;General