Forum Discussion
Custom Engineering number formatting
I would like to create a custom number format for engineering applications that use suffixes: Example: 0.024 => 24 m 2.55E-5 => 25.5 µ 3.7 => 3.7 3700 => 3.7 k I already use the custom forma...
It is a little known fact that a custom number format can contain conditions. The custom format below has three possible formats (separated by a semi-colon): numbers less than -1000, numbers greater than 1000 and the rest:
[<-1000]-##0.00 E-#;[>1000]##0.00 E-#;#,##0.0
If you want to add prefixes, you can consider using a set of conditional formats. Still, you will have to add the unit character manually for each cell with a different unit of measure.
I hope one day Excel will become aware of units of measure, so it might disallow you te try to subtract Amperes from kilometers.
[<-1000]-##0.00 E-#;[>1000]##0.00 E-#;#,##0.0
If you want to add prefixes, you can consider using a set of conditional formats. Still, you will have to add the unit character manually for each cell with a different unit of measure.
I hope one day Excel will become aware of units of measure, so it might disallow you te try to subtract Amperes from kilometers.
- Thank you for your reply. Unfortunately, Excel has a big limitation with conditions (only 2 conditions are allowed) : as mentioned here:
https://stackoverflow.com/questions/25570353/excel-custom-number-format-with-three-conditions
It seems that Excel (and related spreadsheet software) has a fundamental limitation that it can only use two conditionals in custom number formats. Apparently, internally it is using 4 possible display categories: number condition 1, number condition 2, other number, and text. Setting custom number formats can only redefine the two number conditions. You cannot directly control the "other" condition.
And with your proposal, it will work for number having |x| >1 but in the case of 4E-3 it will fail and just write 0.0
The problem is that 2 conditions are really not sufficient to do the job except if there is a way to use a function as ABS() in the condition but I don't think it is possible.- Hence my suggestion to expand on this using conditional formatting rules.
