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...
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.
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.
