Problems with TDIST, TINV, and Related Functions

[fauzie811]

This is:

• [x] a bug report
• [ ] a feature request
• [ ] not a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

It should returns: 12.7062047

What is the current behavior?

It returns: 1.8373862310371

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

$calculation = \PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance();

$formula = '=TINV(0.05, 1)';
$result = $calculation->_calculateFormulaValue($formula);

What features do you think are causing the issue

• [ ] Reader
• [ ] Writer
• [ ] Styles
• [ ] Data Validations
• [x] Formula Calculations
• [ ] Charts
• [ ] AutoFilter
• [ ] Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

Not sure.

Which versions of PhpSpreadsheet and PHP are affected?

2.2

[oleibman]

Confirmed. Calculating this value is done with iterative guesses using the Newton-Raphson method, and we've seen other problems using that method for XIRR. We were able to correct it due to the work of others implementing XIRR in other languages; a cursory search does not reveal similar activity for TINV, so solving this may take a long time.

Also of note - PhpSpreadsheet currently uses the same code for T.INV as TINV, and leaves T.INV.2T unimplemented. In fact, T.INV.2T is supposed to yield the same (hopefully correct) result as TINV, and T.INV needs a new algorithm altogether (and should be marked as unimplemented till we figure out what that is). Similarly, T.DIST.2T, currently unimplemented, should yield the same result as TDIST.

[oleibman]

It actually looks like TDIST might be at fault rather than TINV. In Excel, =TDIST(1.8373862310371,1,2) evaluates to 0.31703; in PhpSpreadsheet, it evaluates to just about 0.05, which is why TINV thinks that that 1.8... is the answer. If that is the case, this might be an easier problem to solve than I'd feared, since TDIST does not use iterative guessing to come up with its result.