problem with FINV function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The Syntax for FINV function in Excel is:

FINV(probability,degrees_freedom1,degrees_freedom2)

When i run this using the values:

FINV(0.025,4198986,1025419)

I get the #NUM! error even thought 4198986 and 1025419 (degrees_freedom1 and
degrees_freedom2 respectively) are < 10^10 as required.

Is there a bug in this function?

Thanks for your help

gfowajuh
 
The focus of this newsgroup is things that cause Excel to crash. This
doesn't. You would get much more and faster response posting in the
Worksheet Functions newsgroup.

Historically, Excel's probability functions (...DIST) have not been very
accurate (typically 5-6 figures if you avoid the extreme tails where they are
completely worthless). Excel's inverse probability functions (...INV)
numerically invert the ...DIST functions, so they can rarely be relied on for
more than 4 figures, and then only for 0.001<p<0.999. That is as good or
better than most tables in basic statistics books, and so plenty good for
routine hypothesis testing, but not much else.

Excel 2003 improved the convergence criteria on the numerical inversion
routines, so in 2003 and later, ...INV functions do a better job of inverting
....DIST functions, but the ...DIST functions themselves still have not been
improved (except for NORMSDIST in 2003).

The working ranges listed in Help for ...DIST and ...INV functions are
extremely optimistic. I am frankly surprised that FDIST can handle df's in
this range. Inverting FDIST therefore is even more problematic. Values for
this problem from various statistics packages include
1.000736 R 2.01
1.00284 S-PLUS 6.2.1
1.00306 Minitab 14
1.00306 Mathematica 5.1
1.00305933035273 JMP 6
1.00305933035341 SAS 8
1.00305933035359 Smith

"Smith" refers to the VBA library of probability functions that you can
download from
http://members.aol.com/iandjmsmith/examples.xls
I cannot verify which is the most accurate value, since Mathematica's
Statistics package ignores the N[] precision operator, and this problem is
outside the working range of Maple 10's FRatio distribution. However, in my
experience, the Smith library is generally to be believed over any other
source that calculates uses IEEE double precision representation of floating
point numbers.

For accuracy of native spreadsheet functions to calculate probability
distributions, I know of no package that beats Spread32 (approaches the
accuracy if not the working range of the Smith library), which may be
downloaded from
http://www.byedesign.freeserve.co.uk
but this is outside its working range for either FINV or FDIST.

Jerry
 
Hello:

The value is just about 1.000. How did you get your degrees of
freedom? Hard to think of an experiment with that many df's.
A test indicates that the Excel FINV function goes south (#NUM) at
approximately 960,000 DF's for 1 and 2. More if you reduce
one and increase the other. The value is about 1.004+ at
these df's. Since the limit is 1.000 any F value greater
than 1.004 is significant for sure. So with that many
df's almost and difference is significant.

Pieter Vandenberg

: The Syntax for FINV function in Excel is:

: FINV(probability,degrees_freedom1,degrees_freedom2)

: When i run this using the values:

: FINV(0.025,4198986,1025419)

: I get the #NUM! error even thought 4198986 and 1025419 (degrees_freedom1 and
: degrees_freedom2 respectively) are < 10^10 as required.

: Is there a bug in this function?

: Thanks for your help

: gfowajuh
 
Back
Top