Move testVlookupWholeColumnRange and providerVlookupWholeColumnRange
from ParseFormulaTest to the correct location VLookupTest, which
extends AllSetupTeardown. Adapted to use $this->getSpreadsheet()
instead of managing the Spreadsheet lifecycle manually.
When resolving the end reference of a whole-column range (e.g. $A:$F),
getHighestDataRow($col) was called for the specific end column. If that
column contains no data it returns 1, producing an inverted range such as
A4:F1. This caused VLOOKUP and similar functions to return #N/A when the
formula used a whole-column reference across sheets where data only exists
in the left-hand columns.
Fix: call getHighestDataRow() without a column argument so the overall
highest data row across all columns is used for the end reference.
Reproducer:
Sheet2!A:D has data in cols A–C only; $A:$F produced A4:F1 → #N/A.
After fix: A1:F4 → VLOOKUP finds the value correctly.
GAMMA.INV silently returned the first bisection midpoint for alpha in
~[143, 171.62] (e.g. GAMMA.INV(0.5, 143, 1) gave 358.0 instead of
142.667) and #NUM! above that, because the Newton-step pdf evaluates
Gamma(a), b**a and value**(a-1) in linear domain, all of which overflow
even though the density itself is a small representable number. The same
pattern breaks the GAMMA.DIST, CHISQ.DIST and F.DIST densities and
GAMMALN, which computed log(Gamma(x)) through Gamma(x).
Evaluate these in log domain via the existing logGamma, and scale the
incomplete-gamma series/continued-fraction iteration cap as O(sqrt(a)),
which both expansions need to converge near x ~ a once the shape is
large (GAMMA.INV drifted from the true quantile above alpha ~5000 and
returned alpha+1 by alpha=10000; same for CHISQ.INV at high df).
calculateInverse() treated a CDF value of exactly 0.0 as "the guess is the
root" and collapsed the bracket with $b = $a, ending the search. inverse()
already rejects probability <= 0, so a CDF of 0.0 inside the bisection can
only be a float64 underflow at a guess far below the root - never an exact
hit. It is just an ordinary "guess too low" and belongs in the existing
else branch.
The underflow is reached whenever a probe lands many standard deviations
from the mean, so the search collapses after two or three iterations and
returns whichever midpoint it was holding. BETAINV(0.5, 5000, 5000) gave
0.25, though Beta(a, a) is symmetric and its median is exactly 0.5;
BETAINV(0.5, 20000, 3) gave 0.5 against a true 0.99986. Onset is around
alpha = 1080 for beta = 1, where the closed form 0.5 ** (1 / alpha) is
available to check against.
That test also happened to stop the search for shapes where incompleteBeta
declines to evaluate at all and returns 0 for every x, so inverse() now
rejects alpha + beta above that documented limit up front: the CDF is
identically zero there, so no quantile exists to search for.
Tests cover a shape x probability grid against scipy reference values plus
three checks that need no external oracle: the symmetric median, the
Beta(alpha, 1) closed form, and the BETADIST round trip / mirror identity.
Merging #4946 (bracket expansion) with #4945 (accurate incomplete
gamma) means p=0.9999999 no longer hits the alpha*beta*5 safety
ceiling -- the corrected regularizedGammaP/Q resolve the true root.
Fold it into the extreme-tail data provider against the closed-form
-ln(1-p) check instead of asserting the stale clamped bound.
It has no remaining callers (kept only for BC) so nothing else exercises
it. Values checked against mpmath's gammainc(a, 0, x); the deep-tail case
(a=4, x=80) reproduces the convergence bug this PR fixes.
The incomplete gamma primitive used a fixed 32-term power series with no
convergence test, so GAMMA.DIST, GAMMADIST, CHISQ.DIST(.RT), GAMMAINV and
CHISQ.INV were grossly wrong once the series argument reached ~32
(e.g. CHISQ.DIST.RT(80, 4) returned 0.806 instead of 1.74e-16).
Replace it with the standard convergence-tested regularized incomplete
gamma: series P(a,x) for x < a+1, continued fraction Q(a,x) for x >= a+1.
CHISQ.DIST.RT now uses Q directly so the right tail stays free of
1 - P cancellation. Consolidates the duplicate copy that already existed
privately in ChiSquared onto the shared primitive.
Many problems with Dependabot this month. Phpstan introduced a lot of new "errors". These are now fixed or annotated. I combined this with a change to require comments for `phpstan-ignore`. These won't always be useful, but I think requiring them makes sense.
Tcpdf is more of a non-update. Our composer.json specified `^6.5`. For some reason, Dependabot decided it was okay to change that to `^6.5||^7.0`, which seems presumptuous. (One of the triggers was probably the elimination of Php8.1, since the new product requires 8.2+.) Tcpdf is nominally deprecated, replaced by tc-lib-pdf. Tcpdf 7 passes control to the new product. However, the upgrade is not straightforward. The user needs to supply font files which were formerly distributed with the product, and a code change to define a (shudder) global constant is required. Consequently, Dependabot's upgrade failed its unit tests. While I may evaluate what might be needed at some point in the future, for now I am just updating composer.json to reject Tcpdf 7+.
GammaBase::calculateInverse fixed its upper bound at alpha*beta*5, so any
GAMMA.INV/GAMMAINV quantile larger than that was clamped to it: e.g.
GAMMAINV(0.9999, 1, 1) returned 5 rather than ~9.2103, breaking the
round-trip GAMMADIST(GAMMAINV(p)) == p.
Grow the upper bound geometrically until it brackets the root. If the CDF
stops increasing first (the series approximation is past its usable range)
keep the original bound instead of expanding into it, which also stops a
probability the series cannot reach from running the bound away.
This PR will be merged at the end of this month. As the README says, we maintain support for Php Versions for six months beyond their end of life. That time has now arrived for 8.1.
Too many errors when Dependabot tried. These seem mostly to involve `implode`, whose use is often embedded in other function calls. For that reason, these changes include a higher proportion than usual of `// @phpstan-ignore-line`.
Fix#1453, which went stale in 2000, which I unstaled in 2024, and which I finally got to. The code, and several unit tests, was substantially generated by AI, my first foray into that frontier.
Trait `ArrayEnabled` is not able to handle this function because it has too many array parameters. I would have liked to update it to handle this, but that seemed too difficult. I have let the code do its own array handling; it looks a little kludgey but seems to do the job. I may return to this at some point.
Fix#4832. PR #4657 added support for passing union arguments to functions. User reports a problem with a peculiar formula afterwards. See issue for details - the very loose restrictions placed on worksheet names can lead to an ambiguous situation for the parser.
This problem arose with a function whose first argument was a cell address *including sheet name*. Parser is changed to try to avoid this situation. If the regular expression which tells us we have a potential need for union has a left parenthesis in the "sheet name" without a right parenthesis, then it will no longer try to treat the formula as containing union arguments except in the unlikely event that "sheet name" truly is a worksheet title in the spreadsheet. This feels pretty kludgey, but it solves the problem at hand, and seems unlikely to cause problems. There may still be edge cases more subtle than the one in the issue; I am satisfied to wait for reports of such.
We have identical constants defined in several places, and use literals in others. We aren't consistent in checking limits. This PR makes the use of the constants in Cell/AddressRange the "official" source, deprecates all other constants, and substitutes the constants wherever literals are used. A number of different edge case tests are added.
During testing, I discovered that `columnIndexFromString` correctly throws an exception for 4-character string, but allows `XFE` through `ZZZ`, all of which are also invalid. There are similar inconsistencies with related routines, and this PR attempts to make them operate consistently. One suprise is that throwing for `row=0` causes serious regression problems, so it continues to be permitted (but the high row limit is enforced).
Further, Reference Helper sometimes dips into negative numbers, resulting in totally unexpected results (-1 affects column Z, -2 column Y, etc.). It is changed to ignore rows and columns outside the limits.
Fix#4061. CONCATENATE, which has slightly different behavior than CONCAT, and which MS has deprecated for that reason, behaves in an unexpected way when a cell range is presented to it and the spreadsheet does not allow for array results. This would almost certainly occur only for Legacy spreadsheets, but such is what was presented in the issue. The code is changed so that when an array of cells is presented to CONCATENATE, and RETURN_ARRAY_AS_VALUE is in effect, the array will be treated as if it were wrapped in the SINGLE pseudo-function (which is what Excel does by somewhat mysteriously prefixing the cell range with `@`).
This is a niche case. This one stands out because of its deprecation and replacement function. It is possible that other functions exhibit this behavior. I have made no attempt to identify others. A similar approach can probably be applied if issues are raised for others.
oleibman
DIReports
Dick Ittmann
Vincent Gao
kemo