Support formatting or rounding to user-specified number of significant figures
I work in a research organization and it's common for us to need to display values to a pre-specified number of significant figures. If you search the internet for "Excel significant figures" you will see a variety of hacks which work to varying degrees. It would be great to have built in support to display to a user-specified number of sig figs. E.g. 1234, displayed to 2 sigfigs, is 1200. Alternately, a built in function which rounds to a specified number of sigfigs would also get the job done.
Madelaine Montilla commented
I agree that this feature has been missing for far too long. Not only is it important to be able to show significant figures easily, but there should be a way for formulas to calculate the significant figures of a CALCULATED field. Could you please consider adding this?
Microsoft is a rentiership machine. They have no interest in improving their products. They just change their GUI and produce huge dislocations for their user base and often regress in usability. They don't know that users want functions and real features, and not GUIs.
Bill Gates is too busy fighting Covid or Malaria or trying to fix the world. If he can't even fix his own products, how can he fix the world? Microsoft Word is an abomination that should be terminated -- but it totally dominates the market due to Microsoft monopoly. The only good thing about Microsoft is Excel.
Years later, and still no format for this. I don't think this would be a complicated thing to add to excel.
There are three ways to handle this, as a formula, as a number format category and as a custom number format. I think that all three are necessary and easy.
The formula would work just like ROUND but truncate to number of significant digits rather than just number of digits. Notice the trailing zeros, they are important.
SIGFIG(2017, 2) = 2000
SIGFIG(2.017, 2) = 2.0
SIGFIG(0.2017, 2) = 0.20
The number format would operate like the Number, Currency, Accounting, and Percentage categories, with a simple selector to determine the number of digits.
The custom number format should look something like: “ !! “ to round to 2 significant figures and “ !!!!! “ to round to 5 significant figures. It would not matter if the number was an integer or a decimal.
2017 with “ !! “ = 2000
2017 with “ !!!!! “ = 2017.0
2.017 with “ !! “ = 2.0
The formula should absolutely truncate the resulting number such that future calculations only use the digits displayed. Whether the number formats truly truncate or just alter what is displayed (while leaving the original value alone) I will leave to brighter minds.
I would also like to see the outlined options dynamically enabled. By this I mean:
round A1 to match the significant digits of cell A2, preferably without having to resort to some LEN formula.
Something like SIGFIG(A1, A2) or SIGFIG(2017, A2)
repost from: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9574854-feature-request-automatic-significant-figures?tracking_code=c1d00dab74a109e7bd8f50131a142f1c
Rounding and formatting are really two different things. Excel should have both options: (1) a "Significant Figures" cell format and (2) a "RoundSignificant(value, numFigures)" function.
The "Significant Figures" cell format needs two settings: (1) the desired number of significant figures to display and (2) what I might call an 'exponential threshold', which is the number of characters at which you switch to exponential notation. I try to avoid exponential notation (especially the format with an "E" instead of 'times ten to a power') because it is harder to read; however, for very large or very small numbers, I prefer to give up and switch to exponential display.
I have struggled with this for 20 years and written functions in VBA and C# to work around this, please add support for significant figures. Microsoft, if you want help, please contact me.
Jan Karel Pieterse commented
@S.Johnson: It is possible to create a custom validation rule that handles situations like this nicely without a problem.
For instance, this rule prohibits the user from entering anything than is not a multiple of 1000: =INT(A1/1000)*1000=A1
Phillip Johnson commented
similar to these two
Phillip Johnson commented
I agree that this feature has been missing for way too long. Please add (1) capability to format the display of cell values to a user specified number of significant figures (also known as significant digits), and (2) functions that round, round-down, round-up to a number of significant figures as an input argument. For reference reading see the following web page which includes links to published papers and more detailed explanation: http://www.angelfire.com/oh/cmulliss/. I am tired of implementing hacks like this: https://www.vertex42.com/ExcelTips/significant-figures.html.
Would love to have this, either as a formula function or as a box. Macros are a no-no in my organization, so UDFs are out of the question. All of the workarounds I've found by google search have the issue of truncated trailing (significant) zeroes. E.g. 0.00296333 to 2 sig figs should be 0.0030, but most of the formulas I've tried result in 0.003. My results can range from 1e-6 to 1e6, so nested ifs with mround get too big for older versions of excel.
JoeU's suggestion returned 0.00: no different from using the "show decimals" formatting option.
Errata.... --TEXT(A1,"0."&REPT("0",B1-1)&"E+0") rounds to B1 significant digits.
Sorry for the late comment.... The built-in function to do this is TEXT with a format of the form "0.0***0E+0", where "***" is metasyntax for the ellipsis. For example, --TEXT(A1,"0E+0") rounds to 1 significant digit, and --TEXT(A1,"0.00000000000000E+0") rounds to 15 significant digits (14 zeros after the decimal point). More generally, --TEXT(A1,"0."&REPT("0",B1)&"E+0") rounds to B1 significant zeros. The double negate ("--") converts text to numeric. Arguably, users might prefer a new built-in that is easier to use, e.g. ROUND.SIG. That can even be expanded to included ROUNDUP.SIG and ROUNDDOWN.SIG.