Number format code to display x significant figures

I know that the number of decimal places can be given by using 0’s. However, I want to be able to display values to 4SF.

1.2345 -> '1.235'
12.345 -> '12.35'
123.45 -> '123.5'
1234.5 -> '1235'
12345  -> '12350'
0.1    -> '0.1000'
1      -> '1.000'
10     -> '10.00'
100    -> '100.0'
1000   -> '1000'

Used in combination with this, I want to ultimately be able to express any value in this way:

1.2345         -> '1.235 '
123456         -> '123.5k'
12345678900000 -> '12.35T'
0.0000012345   -> '1.235μ'

This is for SI units. See metric prefix.

James, I don’t think you can solve your brain teaser just with the formatting function of CALC.

Consider writing a macro/userdefined Function to do it.

I am not sure if I understand “4SF” but I assume you mean 4 digits behind the decimal point.
Then I would use the format 0.#### With this answer I am neglecting all divisions by 1000 and multiplications by 100 because I don’t know yet a solution for avoiding the display of %.

4SF is an abreviation for 4 significant figures. None of the first block of examples given above can be achieved with 0.####, nevermind all of them.

I’ve added some more examples.

@JamesHaigh you can do it with 0.000, 00.00, 000.0 etc. individually except 12345 → 12350. Or use scientific format 0.000E-00 to get your 4SF, works with all numbers.

Let me sketch the work around idea I have.

Use column 1 to enter all the raw data you get,
use column 2 (maybe with IF-statements to identify the multiplier you need) to mulriply the column 1 figures to get the “format” you need
use column 3 to display figure like 1.234mA

You can hide column 2

If above works, yoiu also should be able to combine column 2 and 3.

Hi @JamesHaigh,

Sounds like we’ve gotten pretty close to a complete answer for you, but the formatting remains elusive. Perhaps a ‘feature request’ could solve this at a deeper level inside the code base.

Please file an enhancement bug and provide as much information about this new feature as possible. Don’t forget to mark your bug as an ‘enhancement’. The QA team will be happy to help you triage your feature request in the bugtracker.

Please post a link to any bugs you file in a comment below using the format “fdo#123456”.