B
bgarey
Can I use excel to round operations with measurements to significant figures
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Rick Rothstein
I am not finding your question to be clear.. can you provide a little more
detail as to what you are looking for along with 3 or 4 examples of what you
have and what you expect them to look like after being rounded?
detail as to what you are looking for along with 3 or 4 examples of what you
have and what you expect them to look like after being rounded?
B
bgarey
When adding measurements, the final answer needs to be rounded off to the
least significant place value of the measurements:
The answer to 12.3 + 312.56 should be rounded off to the .10's place.
The answer to 1200 + 346 should be rounded off to the 100's place.
When multiply and dividing, the final answer should be rounded off to the
least number of significant digits.
The answer to 245 x 1.2 should be rounded to 2 digits and adding zero's for
place value if necessasry.
least significant place value of the measurements:
The answer to 12.3 + 312.56 should be rounded off to the .10's place.
The answer to 1200 + 346 should be rounded off to the 100's place.
When multiply and dividing, the final answer should be rounded off to the
least number of significant digits.
The answer to 245 x 1.2 should be rounded to 2 digits and adding zero's for
place value if necessasry.
J
Jim Thomlinson
The short answer is no... The problem that you run up against with this type
of rounding is that there is no definitieve way to assess the least
significant digit. In your example with 1200 you indicate that the result
should be rounded to the nearest 100. Your assumption is that 1200 is only
accurate to the nearest 100 units of measure. It could actually be accurate
to the nearest single unit. 1201 would be accurate to the nearest single
unit. To be truely accurate you either need to know the the accuracy of the
least significant digit, or if you have a large enough data set of repeated
mesurements that you can use stats to determine the least significant digit.
of rounding is that there is no definitieve way to assess the least
significant digit. In your example with 1200 you indicate that the result
should be rounded to the nearest 100. Your assumption is that 1200 is only
accurate to the nearest 100 units of measure. It could actually be accurate
to the nearest single unit. 1201 would be accurate to the nearest single
unit. To be truely accurate you either need to know the the accuracy of the
least significant digit, or if you have a large enough data set of repeated
mesurements that you can use stats to determine the least significant digit.
R
Rick Rothstein
My recollection about measurement "theory" is that 1200 has four significant
digits of accuracy because it is presented using four digits (the assumption
being it was measured to the last shown digit). On the other hand, had it
been presented as 1.2x10², then there are only two significant digits (the
power of ten is immaterial) and, as I recall, in a calculation of
measurements, the answer should be rounded to the least of the significant
digits among the values making up the calculation.
digits of accuracy because it is presented using four digits (the assumption
being it was measured to the last shown digit). On the other hand, had it
been presented as 1.2x10², then there are only two significant digits (the
power of ten is immaterial) and, as I recall, in a calculation of
measurements, the answer should be rounded to the least of the significant
digits among the values making up the calculation.
F
Fred Smith
When I took significant figures, we differentiated it as follows:
1. 1200 is 2 sig figs
2. 1200. is 4 sig figs (note the decimal point)
3. 1.20 x 10^3 is 3 sig figs
Regards,
Fred.
1. 1200 is 2 sig figs
2. 1200. is 4 sig figs (note the decimal point)
3. 1.20 x 10^3 is 3 sig figs
Regards,
Fred.
B
bgarey
This is all correct. But is there away to get Excel to round to the correct
place value.
I know the TI-83 calculators have an application that can round to correct
significant figures. I have no idea how to do this.
place value.
I know the TI-83 calculators have an application that can round to correct
significant figures. I have no idea how to do this.
B
bgarey
Actually I can get addition to work. I have not found away to apply the
multiplication and division rule to apply.
Excel needs to count the number of significant figures in each number, then
apply the minimum number to the answer in rounding.
multiplication and division rule to apply.
Excel needs to count the number of significant figures in each number, then
apply the minimum number to the answer in rounding.
J
Jerry W. Lewis
About 10 years ago I posted the formula
=ROUND(A1, A2 - 1 - INT(LOG10(ABS(A1))))
to round A1 to A2 significant figures.
It originally looked as though you were asking if there was a way for Excel
to determine A2 by formula instead of by human input--that would be a much
more difficult question.
Jerry
=ROUND(A1, A2 - 1 - INT(LOG10(ABS(A1))))
to round A1 to A2 significant figures.
It originally looked as though you were asking if there was a way for Excel
to determine A2 by formula instead of by human input--that would be a much
more difficult question.
Jerry
B
bgarey
This will be very helpful. I still need Excel to be able to count the number
of significant digits in a measurement.
For instance:
.000340 has 3 significant digits. The leading zeros are place holders.
34,000 has 2 significant figures. The trailing zeros are place holders.
of significant digits in a measurement.
For instance:
.000340 has 3 significant digits. The leading zeros are place holders.
34,000 has 2 significant figures. The trailing zeros are place holders.
B
bgarey
I should of added this in to my previous message. If I multiply the values
together, the answer should have the minimum number of significant figures,
ie, 2.
.00340 x 34,000 = 116 should be rounded to 120
together, the answer should have the minimum number of significant figures,
ie, 2.
.00340 x 34,000 = 116 should be rounded to 120
J
Jerry W. Lewis
Glad the formula helped. As I said, having Excel determine the number of
significant figures in a number is a difficult problem:
- You will almost certainly need to implement it in VBA, with custom
functions for every supported mathematical operation (addition, subtraction,
multiplication, etc) since to Excel (and almost all other software), a number
is a number, and it carries no memory of what calculation produced it.
- You will either have to work with text representation of numbers or else
parse cell formats, since numerically there is no distinction between 34000
and 34000. to decide whether that number has 2 or 5 significant figures.
- You will need to beware of the impact of binary representations on
numbers, particularly if there are unrounded calculated values as inputs.
For example 2.3-2.2-0.1 is correctly non-zero when you consider the binary
representations of the numbers involved. For rounding purposes, I often find
it convenient to go through an intermediate string representation, such as
the VBA CDbl(CStr(x))
- If you try to avoid VBA, you will need to beware of an Excel display bug
that may cloud the issue. Millions of numbers display in Excel with 15
significant figures even though fewer were entered.
http://support.microsoft.com/kb/161234 mentions only one 3-figure decimal
fraction that is impacted over a limited range of numbers each intended to
display with 8 figures. In fact there are millions of decimal fractions
(AFAIK at least 3 decimal places and at least 8 figures total
http://groups.google.com/group/microsoft.public.excel.misc/msg/1b2d9f986ce8e65b)
so affected in Excel versions prior to 2007. If you use 2007, be sure to
include service patches through Oct 9,2007 to fix a more serious newly
introduced display issue http://support.microsoft.com/kb/943075
Jerry
significant figures in a number is a difficult problem:
- You will almost certainly need to implement it in VBA, with custom
functions for every supported mathematical operation (addition, subtraction,
multiplication, etc) since to Excel (and almost all other software), a number
is a number, and it carries no memory of what calculation produced it.
- You will either have to work with text representation of numbers or else
parse cell formats, since numerically there is no distinction between 34000
and 34000. to decide whether that number has 2 or 5 significant figures.
- You will need to beware of the impact of binary representations on
numbers, particularly if there are unrounded calculated values as inputs.
For example 2.3-2.2-0.1 is correctly non-zero when you consider the binary
representations of the numbers involved. For rounding purposes, I often find
it convenient to go through an intermediate string representation, such as
the VBA CDbl(CStr(x))
- If you try to avoid VBA, you will need to beware of an Excel display bug
that may cloud the issue. Millions of numbers display in Excel with 15
significant figures even though fewer were entered.
http://support.microsoft.com/kb/161234 mentions only one 3-figure decimal
fraction that is impacted over a limited range of numbers each intended to
display with 8 figures. In fact there are millions of decimal fractions
(AFAIK at least 3 decimal places and at least 8 figures total
http://groups.google.com/group/microsoft.public.excel.misc/msg/1b2d9f986ce8e65b)
so affected in Excel versions prior to 2007. If you use 2007, be sure to
include service patches through Oct 9,2007 to fix a more serious newly
introduced display issue http://support.microsoft.com/kb/943075
Jerry
J
Jerry W. Lewis
A partial solution would be the array formula (commit with Ctrl+Shift+Enter)
=MIN(IF(ROUND(x,14-INT(LOG10(ABS(x))))=ROUND(x,ROW($A$1:$A$617)-309),ROW($A$1:$A$617)-309))+INT(LOG10(ABS(x)))+1
which will return the number of significant figures ignoring significant
trailing zeros. Since there is no numeric difference between 0.00034 and
0.000340, the formula will return 2 in both cases. Similarly it will return
2 for both 34000 and 34000. since again there is no numerical difference
between them. As noted earlier, you would either have parse text input of
the numbers or parse the cell format to recognize trailing significant zeros.
Jerry
=MIN(IF(ROUND(x,14-INT(LOG10(ABS(x))))=ROUND(x,ROW($A$1:$A$617)-309),ROW($A$1:$A$617)-309))+INT(LOG10(ABS(x)))+1
which will return the number of significant figures ignoring significant
trailing zeros. Since there is no numeric difference between 0.00034 and
0.000340, the formula will return 2 in both cases. Similarly it will return
2 for both 34000 and 34000. since again there is no numerical difference
between them. As noted earlier, you would either have parse text input of
the numbers or parse the cell format to recognize trailing significant zeros.
Jerry
J
Jerry W. Lewis
As an Excel array formula in Excel versions prior to 2007, values of 15 from
this formula are not to be trusted.
Excel's ROUND function apparently stabilizes the binary representation by
intermediate conversion to text, and so is impacted by the millions of
decimal fractions (AFAIK a subset of numbers with >=3 decimal places and >=8
sig figs overall) that is very minimally acknowledged by
http://support.microsoft.com/kb/161234
VBA's Round function (Excel 2000 and later) is not impacted by this Excel
display bug, but it also does not natively support array formulas. You could
implement it as a VBA loop and get results that I would expect to be reliable
for all numbers. You would have to work a bit harder though, since VBA's
Round function does not directly support rounding to negative decimal places.
You may find
http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69 to be helpful.
Jerry
this formula are not to be trusted.
Excel's ROUND function apparently stabilizes the binary representation by
intermediate conversion to text, and so is impacted by the millions of
decimal fractions (AFAIK a subset of numbers with >=3 decimal places and >=8
sig figs overall) that is very minimally acknowledged by
http://support.microsoft.com/kb/161234
VBA's Round function (Excel 2000 and later) is not impacted by this Excel
display bug, but it also does not natively support array formulas. You could
implement it as a VBA loop and get results that I would expect to be reliable
for all numbers. You would have to work a bit harder though, since VBA's
Round function does not directly support rounding to negative decimal places.
You may find
http://groups.google.com/group/microsoft.public.excel.charting/msg/107fce6145b70d69 to be helpful.
Jerry
R
Ron Rosenfeld
This will be very helpful. I still need Excel to be able to count the number
of significant digits in a measurement.
For instance:
.000340 has 3 significant digits. The leading zeros are place holders.
34,000 has 2 significant figures. The trailing zeros are place holders.
I think this is one way that will work, according to your rules.
The routine depends on the textual representation of your values being
accurate. So, for one think, if the number has trailing zero's, you will need
to enter it as "text". Cell formatting with a number of zeros will not work --
you must either precede the entry with a single quote, or pre-format the cell
as text.
1. Download and install Longre's free morefunc.xll add-in from
2. This formula should give the number of significant digits. Let me know if
it does not:
=LEN(REGEX.SUBSTITUTE(A1:A9,"^[0.]+|^([^0.]+)0+$|\.","[1]"))
The regular expression pattern "^[0.]+|^([^0.]+)0+$|\." matches those zero's
that are not significant, and also matches the decimal point, removing them
from the string.
The REGEX... function can also return an array, so if you have a list of
numbers (one to a cell) and want to return the minimum number of significant
digits, you could use this formula entered as an **array** formula with
<ctrl><shift><enter>:
=MIN(LEN(REGEX.SUBSTITUTE(rng,"^[0.]+|^([^0.]+)0+$|\.","[1]")))
rng is a cell reference to multiple cells. e.g. A1:A2
--ron
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.