Ok,
It's been a while since I've been on the boards. When I was more active, we had a rousing discussion on Significant Figures:
http://www.mrexcel.com/forum/excel-questions/64212-significant-figures-problem.html
Interestingly enough, I've seen the solution that we came up with here trickle through the scientific community. We often find ourselves bending Excel to our scientific needs rather than the mathematical ones.
Today, I discovered a new fun problem. I have a spreadsheet that performs the following calculation
A-(B*1000)
This is a spreadsheet that users fill out as they are performing work. It is then transferred to another database system using an automated process that I have written. Everything works great except for.... You guessed it... significant figures.
Take the following example:
13.170-(0.1310*1000) = 0.07000
That is all fine and dandy. But when I export the data in to another system, the data that goes forward is
0.0699999999999985
I'm 99% sure that it has to do with the precision of the numbers used in the calculations performed by Excel.
We try to capture as many decimal places as possible in the second system that data is transfered to even though they may not be "significant" (see previous post if you have a burning desire to understand that
).
So my question is... short of rounding the values prior to the transfer, is there any other fix for this?
If I round, what is the proper number decimal places to round to? Is it constant? Or does it depend on the number?
Finally, I (mostly) understand why this is happening... and our reports that are going out to the customer are correct (rounded to the proper number of significant figures). How do I explain to my bosses the "why" of this occurance?
It's been a while since I've been on the boards. When I was more active, we had a rousing discussion on Significant Figures:
http://www.mrexcel.com/forum/excel-questions/64212-significant-figures-problem.html
Interestingly enough, I've seen the solution that we came up with here trickle through the scientific community. We often find ourselves bending Excel to our scientific needs rather than the mathematical ones.
Today, I discovered a new fun problem. I have a spreadsheet that performs the following calculation
A-(B*1000)
This is a spreadsheet that users fill out as they are performing work. It is then transferred to another database system using an automated process that I have written. Everything works great except for.... You guessed it... significant figures.
Take the following example:
13.170-(0.1310*1000) = 0.07000
That is all fine and dandy. But when I export the data in to another system, the data that goes forward is
0.0699999999999985
I'm 99% sure that it has to do with the precision of the numbers used in the calculations performed by Excel.
We try to capture as many decimal places as possible in the second system that data is transfered to even though they may not be "significant" (see previous post if you have a burning desire to understand that
). So my question is... short of rounding the values prior to the transfer, is there any other fix for this?
If I round, what is the proper number decimal places to round to? Is it constant? Or does it depend on the number?
Finally, I (mostly) understand why this is happening... and our reports that are going out to the customer are correct (rounded to the proper number of significant figures). How do I explain to my bosses the "why" of this occurance?
