对齐数值精度Excel 2013和R [英] Align numerical precision Excel 2013 and R
问题描述
这是一个相当广泛的问题，但是我在R中复制了一个Excel模型.R产生的结果与excel几乎相同，但是在10 ^ -8的范围内总是存在％的偏差.我认为这是由于数值精度.该模型仅使用基本算术运算.我的问题是:是否有一种简单的方法可以强制R使用与Excel相同的数值精度?
It is a rather broad question, but I replicated an Excel model in R. R produces almost the same results as excel, but there is always a slight % deviation in the range of 10^-8. I assume this is due to numerical precision. The model uses only basic arithmetic operations. My question would be: is there a simple way to force R to use the same numerical precision as Excel?
推荐答案
可能不是.R始终使用双精度(即8字节)浮点，并且似乎这也是Excel所使用的.
Probably not. R always uses double-precision (i.e., 8-byte) floating point, and and it seems that's also what Excel uses.
如果仅执行简单的算术运算，结果并不完全相同，这确实令人感到有些惊讶，但是请注意，即使更改算术运算的顺序也可以更改结果(即，浮点算术不具有关联性:(a + b)+ c！= a +(b + c)，因此，如果从左到右进行算术运算，则 a + b + c！= c + b + a ).尽管这(可能仅适用于Excel 2003)指出Excel偏离R和大多数其他数字程序使用的IEEE 754标准的点.
It is indeed mildly surprising that the results are not identical if you're only doing simple arithmetic, but note that even changing the sequence of arithmetic operations can change the results (i.e., floating-point arithmetic is not associative: (a+b)+c != a+(b+c), so if arithmetic is done left-to-right, a+b+c != c+b+a). It's probably impossible to even know what Excel is doing under the hood, although this (which may? only apply to Excel 2003) states points at which Excel deviates from the IEEE 754 standard used by R and most other numerical programs.
您可能可以通过从简单到更复杂的示例进行工作，查看出现差异的位置，并重新安排计算以避免出现问题，从而获得精确的相等性，但是除非这种相等性非常重要如果您/值得投入大量的精力，则最好进行近似相等的测试.
You might be able to get exact equality by working up from simple to more complex examples, seeing where the divergence occurs, and rearranging your computations to avoid the problem, but unless this equality is extremely important to you/worth investing a lot of effort, you may be better off testing for approximate equality.
It's conceivable that export to CSV is losing precision (e.g. see here).
这篇关于对齐数值精度Excel 2013和R的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
