have a setting for number of significant figures rather than just for number of numbers to the right of the decimal
Currently, format can be set for the number of digits to the right of the decimal. Chemical data can vary over several orders of magnitude for a single parameter, but only 2 or 3 digits are valid as significant figures. Therefore, it would be handy to be able to format cells to show a specific number of significant figures. For example, 0.094686 has 6 digits to the right of the decimal, but if the analysis only has two significant figures, the posted value should be 0.095.
There should be a function that determines the number of significant figures of a particular cell's contents. There should also be a function that uses the number of significant figures determined by the first function to round off calculations performed on the contents of the first cell mentioned. For example: Cell A1 has a value of 12.315. The first function might look something like this: =sigfigs(A1). The value determined by the function would be 5. Then let's say I want to multiply the value in cell A1 by pi (3.14159...) with the result also having 5 significant figures because the value of A1 had 5 sigfigs. The result should be 38.689 which is rounded off to 5 sig figs. The second function might look something like this: =sigfigcalc(A1*3.14159,sigfigs(A1)). Currently I have to do all of that by hand, which is a real pain when I have hundreds of calculations to do.
I just want to add numbersd
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
Phillip Johnson commented
Phillip Johnson commented
I agree that a feature like this 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.
Kenneth Barber commented