Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
Can I use excel to round operations with measurements to significant figures
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
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? -- Rick (MVP - Excel) "bgarey" wrote in message ... Can I use excel to round operations with measurements to significant figures |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
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. "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "bgarey" wrote in message ... Can I use excel to round operations with measurements to significant figures |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
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. -- HTH... Jim Thomlinson "bgarey" wrote: 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. "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "bgarey" wrote in message ... Can I use excel to round operations with measurements to significant figures |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
How would you distinguish 1200 measured to 2 significant figures from 1200
measured to 3 or 4 significant figures? -- David Biddulph "bgarey" wrote in message ... 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. "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "bgarey" wrote in message ... Can I use excel to round operations with measurements to significant figures |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
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. -- Rick (MVP - Excel) "Jim Thomlinson" wrote in message ... 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. -- HTH... Jim Thomlinson "bgarey" wrote: 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. "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "bgarey" wrote in message ... Can I use excel to round operations with measurements to significant figures |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
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. "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... How would you distinguish 1200 measured to 2 significant figures from 1200 measured to 3 or 4 significant figures? -- David Biddulph "bgarey" wrote in message ... 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. "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "bgarey" wrote in message ... Can I use excel to round operations with measurements to significant figures |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
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. "Fred Smith" wrote: 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. "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... How would you distinguish 1200 measured to 2 significant figures from 1200 measured to 3 or 4 significant figures? -- David Biddulph "bgarey" wrote in message ... 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. "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "bgarey" wrote in message ... Can I use excel to round operations with measurements to significant figures |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
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. "bgarey" wrote: 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. "Fred Smith" wrote: 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. "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... How would you distinguish 1200 measured to 2 significant figures from 1200 measured to 3 or 4 significant figures? -- David Biddulph "bgarey" wrote in message ... 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. "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "bgarey" wrote in message ... Can I use excel to round operations with measurements to significant figures |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
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 "bgarey" wrote: 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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
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. "Jerry W. Lewis" wrote: 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 "bgarey" wrote: 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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
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 "bgarey" wrote: 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. "Jerry W. Lewis" wrote: 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 "bgarey" wrote: 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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
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/micro...2d9f986ce8e65b) 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 "bgarey" wrote: 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. "Jerry W. Lewis" wrote: 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 "bgarey" wrote: 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. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
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 "bgarey" wrote: 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. "Jerry W. Lewis" wrote: 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 "bgarey" wrote: 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. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
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/micro...7fce6145b70d69 to be helpful. Jerry "Jerry W. Lewis" wrote: 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 "bgarey" wrote: 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. "Jerry W. Lewis" wrote: 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 "bgarey" wrote: 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. |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
significant figures?
On Wed, 3 Sep 2008 09:08:10 -0700, bgarey
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format cells number to 3 significant figures?? | Excel Discussion (Misc queries) | |||
How to display results to certain number of significant figures | Excel Discussion (Misc queries) | |||
Automatic formatting of Significant Figures .... | Excel Discussion (Misc queries) | |||
Rounding/Significant figures | Excel Worksheet Functions | |||
Significant figures (not decimal places) | Excel Worksheet Functions |