在Microsoft Excel中获取机器epsilon [英] Get Machine epsilon in Microsoft Excel

问题描述

这似乎是一个更好的针对具有一些编程经验的人的问题，而不仅仅是一般的Excel用户，因此我在这里询问而不是超级用户。

有没有办法，最好是通过一个函数来返回epsilon（即在Excel的计算中可以表示的最小的非零数）？如果它不能通过函数检索，是否有一个快速的方式通过一个紧凑的功能来计算？

要清楚，我不是在寻找基于VBA的解决方案，我想要一个不需要运行宏的基于Excel公式/电子表格的解决方案。

我的在线搜索主要讨论了如何显示符号Epsilon。

谢谢。

解决方案

Excel总是以IEEE双打操作。

< VBA表达式将使您得到大于0的最小非非归一化浮点数是

  = 2 ^ -1022 
  

另一方面，机器epsilon是可以添加到1的最小数字，并产生一个数字大于1.对于双重精度，它由

  = 2 ^ -52 
  

This seems like a question better directed at those with some programming experience rather than just general Excel users, hence my asking on here as opposed to Superuser.

Is there any way, preferably through a function, to return epsilon (i.e. the smallest non-zero number representable in Excel's calculations)? If it's not retrievable through a function, is there a quick way to calculate it through a compact function?

To be clear, I'm not looking for a VBA-based solution, I'd like an Excel formula/ spreadsheet based solution that does not require running macros.

My searches online have mostly turned up discussions on how to display the symbol Epsilon.

Thanks.

解决方案

Excel always operates on IEEE doubles.

A non-VBA expression that will get you the smallest non-denormalized floating point number greater than 0 is

=2^-1022

Machine epsilon, on the other hand, is the smallest number that can be added to 1 and result in a number that is greater than 1. For double precision it is given by

=2^-52

这篇关于在Microsoft Excel中获取机器epsilon的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！