使用COMBIN功能的奇数结果 [英] Odd results using the COMBIN function

问题描述

我是一名数学学生，并获得了一个详细的证明。链接。

出于好奇我尝试在Excel表中设置列，列为N，行为K，因此那个：

  = COMBIN（R $ 6，$ B16）-COMBIN（R $ 6-1，$ B16）-COMBIN（R $ 6-1，$ B16-1）
  

应该等于 0 。在大多数情况下，它有效，但有时我会得到奇怪的结果。例如：

  = COMBIN（110,35）-COMBIN（110-1,35）-COMBIN（110-1， 35-1）= 0 
  

和

  = COMBIN（110,45）-COMBIN（110-1,45）-COMBIN（110-1,45-1）= 0 
  

但

  = COMBIN（110,40） -COMBIN（110-1,40）-COMBIN（110-1,40-1）= 633318697598976 
  

有人可以告诉我为什么会发生这种情况吗？

解决方案

恐怕答案是Excel缺乏精度（最多15位数），但也许这不是一个很好的答案。

Excel's = COMBIN（）函数 A2 （说110）和 B2 （说35）可以分解为：

 code> = FACT（A2）/（FACT（A2-B2）* FACT（B2））
  

下面的图片的顶部显示了这一点（并确认了您的结果！）：

底部将该公式分解为其组件，显示最大元素的值为1.5882E + 178。这在Excel的公式为+/- 1.7976931348623158e + 308的限制之内，但可能实际上是在以下之间：

15882455415227300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

和

1588245541522750000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

我猜是这个问题。

$ b $令人困惑的是，我坚持使用110，差异仅在 B2 范围1-109中仅出现29次 - 但是对于任何解释最好回到数学！

I am a mathematics student and was given a proof detailed in this link.

Out of curiosity I tried setting that up in a an Excel table with columns as N and rows as K, so that:

=COMBIN(R$6,$B16)-COMBIN(R$6-1,$B16)-COMBIN(R$6-1,$B16-1)

should equal 0. For the most part it does but sometimes I get odd results. For example:

=COMBIN(110,35)-COMBIN(110-1,35)-COMBIN(110-1,35-1)=0

and

=COMBIN(110,45)-COMBIN(110-1,45)-COMBIN(110-1,45-1)=0

but

=COMBIN(110,40)-COMBIN(110-1,40)-COMBIN(110-1,40-1)=633318697598976

Can somebody tell me why this is happening?

解决方案

I’m afraid the answer has to be the lack of precision in Excel (15 digits max) but maybe that is not really a good enough answer.

Excel’s =COMBIN() function for A2 (say 110) and B2 (say 35) can be broken down as:

=FACT(A2)/(FACT(A2-B2)*FACT(B2))

The top part of the image below shows this (and confirms your results!):

The bottom part breaks that formula down into its components, showing that the largest element has a value 1.5882E+178. That is well within Excel’s limit for formulae of +/-1.7976931348623158e+308 but presumably is actually something between:

15882455415227300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

and

15882455415227500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

which I guess is the problem.

What puzzles me is that sticking with 110, the discrepancies arise only 29 times in the B2 range 1-109 – but for any explanation of that it may be best to revert to Mathematics!

这篇关于使用COMBIN功能的奇数结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！