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.

 

[shg]

... trust a formula more than conditional formatting i think??

I'd probably use your conditional formatting, so it could be implemented in situ. I wrote that before Excel 2007, when number formatting was not available in conditional formatting.