FORECAST.ETS.PI.ADD in Collabora Office: Additive Prediction Intervals

=FORECAST.ETS.PI.ADD(target; values; timeline; [confidence_level]; [period_length]; [data_completion]; [aggregation])

Formulas here use English worksheet names and semicolon separators. Square brackets describe optional arguments and are not typed. The first three arguments are target, historical values and historical timeline. Confidence occupies the fourth position, before period_length. Use an explicit confidence strictly between 0 and 1, such as 0.95. Help brackets confidence as optional but calls it mandatory in prose; the wrapper allows omission and supplies 0.95. Its endpoint range check also differs from Help. Do not use 0 or 1 as supported confidence examples.

Additive seasonality adds a seasonal adjustment to level plus trend. It is a useful candidate when the seasonal swing stays roughly constant in the same measurement units as the observations. A visually additive pattern does not establish the best fitted model or its future accuracy.

Use two numeric columns with one value per timestamp, equal-sized ranges, and headers outside those ranges. Sort the two columns together if you sort them yourself. Collabora documents internal timeline sorting, but sorting only one column changes which observation belongs to each time. Use a consistent positive timeline step; any intended gaps must be whole multiples of that step. A measured zero belongs in the values column. An absent timestamp is a different data problem.

The fixtures below use simple integer time indexes to avoid locale-dependent dates. For monthly records, enter real dates such as =DATE(2025;1;1), not ambiguous text such as 01/2025. The pinned implementation recognizes same-day-of-month dates and converts them to month units. This special handling does not license arbitrary irregular timestamps. Month-end transitions, time fractions and monthly gaps need separate target-build checks.

For functions with period_length, use an integer: 0 explicitly selects nonseasonal double exponential smoothing (EDS); 1 requests automatic detection; 2 or more requests that many samples per seasonal cycle. The omitted default is 1. A value of 1 does not mean a literal one-observation season. Some Help example descriptions say “one sample per period,” but that conflicts with the formal parameter description. A four-quarter cycle means 4 samples; a twelve-month cycle means 12, provided the input cadence actually matches.

The ETS family exposes data_completion and aggregation. Completion defaults to 1 (TRUE); 0 (FALSE) requests zero filling. The documented duplicate-timestamp choices are 1 AVERAGE (default), 2 COUNT, 3 COUNTA, 4 MAX, 5 MEDIAN, 6 MIN and 7 SUM. Use these integer codes, not function names in that argument. Numeric COUNT/COUNTA semantics do not establish acceptance of text observations. These examples deliberately contain no gaps or duplicates, so changing their cleanup options should not change the input dataset.

Do not quietly rely on default AVERAGE to repair duplicate timestamps. In the pinned Collabora implementation, case 1 removes later equal-timestamp rows without computing their mean. The Help describes AVERAGE instead. For duplicate observations 90 and 150, the arithmetic mean is 120 and sum is 240; neither calculation establishes which observation the affected default branch retains after sorting. Resolve duplicates deliberately upstream, or verify the intended aggregation against the exact build before trusting a forecast.

Help calls completion by 1 interpolation. The saved source calculates a single mean of the endpoints for each missing block. With values 60 at time 3 and 90 at time 6, linear interpolation would insert 70 and 80 at times 4 and 5; that source instead constructs 75 and 75. These are independent preprocessing arithmetic controls, not native observations. Help’s “up to 30%” description also leaves the denominator unclear: the source divides inserted missing points by the original supplied count after duplicate handling. Two missing points and fourteen supplied points give 2/14, not 2/16. Avoid threshold claims without a target test.

Range-shape, numeric-content and option errors are also documentation-versus-runtime questions. Help describes particular spreadsheet error labels, but the source sometimes raises a different internal error category, including the unequal-range path. No exact native error display is verified here. Fix the paired data and integer options first; do not use this guide as an error-code compatibility table. Fractional options, blank-cell coercion, array-entry gestures and automatic spill behavior remain unverified.

Enter the following synthetic teaching data in A1:B17. Keep the headers in row 1; the formulas use only the sixteen numeric observations. The same data are used in the ADD and MULT guides so model comparisons do not change the history.

The four block means are 96.25, 119.5, 144.5 and 168.25. Their ranges are 50, 59, 71 and 83: seasonal variation grows broadly with the level, with deliberate noise. This makes a useful comparison between fixed-size additive adjustments and proportional multiplicative adjustments; it does not predetermine the best future model.

How to read the numbers: all fitted values below are independently checked calculations from the pinned Collabora source model, rounded for display. They are not observed results from an installed Collabora application. The history is complete, positive and uniquely timed, the timeline step is 1, and the seasonal period is explicitly 4. The checked mathematical harness does not execute worksheet parsing, error displays or the application interface.

For a one-step-ahead forecast, enter this center formula in D2:

=FORECAST.ETS.ADD(17;$B$2:$B$17;$A$2:$A$17;4;1;1)

Enter the matching radius formula in E2:

=FORECAST.ETS.PI.ADD(17;$B$2:$B$17;$A$2:$A$17;0.95;4;1;1)

Then enter =D2-E2 in F2 for the lower bound, =D2+E2 in G2 for the upper bound, and =2*E2 in H2 for total width. Reuse the stored E2 radius rather than making independent simulation calls inside each bound. Both formulas use the same target, history, model, period and cleanup options. The radius is in the observation units; it is neither an endpoint nor the full width.

The following are one recorded source-model simulation per scalar target, not promised worksheet outputs. Each scalar call starts a fresh model and a newly reset, explicitly specified demonstration draw stream. The point column is the independently checked deterministic source-model forecast.

For target 17, the demonstration uses center 148.638780 and radius 7.275899, producing the displayed bounds and total width 14.551798. All endpoint arithmetic uses unrounded values before display rounding. Changing the target to 18 or 20 requires changing it in both formulas; the remaining rows illustrate those separate scalar calls. No scalar value here was borrowed from a multi-target array call.

Collabora Help describes 1,000 random scenarios. The pinned seasonal algorithm takes the empirical 0.975 percentile minus the median when confidence is 0.95, using inclusive percentile ranks at zero-based positions 974.025 and 499.5 among 1,000 sorted scenario outcomes. The source draws U between 0.5 and 1 and computes fitted RMSE times its inverse-normal function. These innovations are nonnegative. This is a specific source algorithm, not a conventional symmetric zero-mean Gaussian simulation; it was not silently corrected to one.

For reproducibility of this mathematical demonstration only, each call resets an LCG to seed 20261019, advances state by (1664525*state + 1013904223) mod 2^32, and maps it to U = 0.5 + (state + 0.5)/2^33. The order is scenario-major, with one draw per intermediate horizon. A scalar target h steps ahead therefore consumes 1,000*h draws: 1,000 at h=1, 2,000 at h=2 and 4,000 at h=4. ADD and MULT use identical draws at a given horizon. This stream is not Collabora’s native RNG, and the worksheet has no demonstrated interface for injecting it. The table teaches a reproducible calculation, not a procedure for forcing those exact radii from the application.

A target matrix whose maximum horizon is eight instead gives each scenario eight draws. Its first-horizon column consequently uses a different subsequence from a separate scalar h=1 call. Do not compare exact radii across different target shapes as though the random inputs were identical. Array entry and spill behavior were not tested.

The point forecast and seasonal simulation also use distinct seasonal offsets at a full-period horizon in this source. Their centers need not coincide. Adding and subtracting the returned radius from the matching point forecast is transparent interval arithmetic; neither that arithmetic nor the confidence argument establishes calibrated 95% coverage. The radius is not a guarantee, a probability that the point forecast is correct, or protection against structural change. Different draw streams can change it, and increasing horizon need not increase a particular recorded radius. Recalculation behavior in an installed build has not been observed here.

Keep the same noisy data but explicitly set period to 0 in both formulas:

=FORECAST.ETS.ADD(17;$B$2:$B$17;$A$2:$A$17;0;1;1)
=FORECAST.ETS.PI.ADD(17;$B$2:$B$17;$A$2:$A$17;0.95;0;1;1)

Both ADD and MULT then follow the nonseasonal EDS calculation. The source-model center is 189.702090, RMSE is 22.495225, and the analytic radius is 44.089831. Its arithmetic bounds are 145.612259 and 233.791922. At the first future step, the source’s coefficient ratio cancels, leaving the 0.975 standard-normal quantile times RMSE. At the second step the source-model radius is 71.654649. At later steps, preserve the implementation’s coefficient expression, which uses confidence itself rather than fitted alpha. A textbook substitute would not be this implementation.

This analytic control is deterministic on the bounded mathematical inputs, but it remains a source-model result rather than an installed output. It is supplementary: disabling the seasonal cycle is a substantial model change, not a way to improve the seasonal estimate. A perfect fitted line can produce zero analytic radius; that would be a synthetic fitting property rather than evidence of certain real-world outcomes.

Use a strictly future integer target and an explicit seasonal period such as 4, or literal 0 for the analytic control. Do not use the last observed target, historical targets, fractional horizons or confidence endpoints as ordinary interval examples. Confidence is explicitly 0.95 here to avoid the Help’s conflicting optional/mandatory descriptions and endpoint wording.

The wrapper dispatches to EDS intervals only when the supplied period is literally 0. Automatic selection can internally fall back to EDS while still entering the seasonal PI routine; a missing seasonal-buffer risk remains in that route. Do not run automatic-fallback PI as a diagnostic or treat it as equivalent to period 0. This guide supplies no default/automatic PI execution recipe. The default period 1 is documented for contract completeness, not endorsed as a safe tested interval path.

For planning, record model assumptions alongside bounds, compare future actual observations with the saved forecasts over time, and investigate systematic misses. Such monitoring is a recommended evaluation workflow; no coverage study or native recalculation experiment was performed here.

The source uses its own nested search for alpha, beta and gamma, including endpoint ties and a resolution of 0.001. This is not a promise of a globally optimal statistical fit. It retains seasonal-buffer changes between trial fits; restarting those buffers for every trial changes the algorithm. Its additive initialization divides the first observation by the initial seasonal index, and its ADD versus MULT seasonal-index tests differ at the period boundary. The source-model calculation preserves these details rather than replacing them with a textbook Holt-Winters implementation.

The future seasonal lookup also has a specific modulo offset, including at a whole-period horizon. Do not substitute another product’s ETS coefficients, generic library predictions or hand-extended block means and call them Collabora results. Refit after correcting observations, but compare the same history, period and model before drawing conclusions from a change.

• Different-sized or nonnumeric history ranges: check both ranges exclude headers and still pair each value with its timestamp. The wrapper rejects incompatible shapes/numeric matrices; its internal error category is not a verified on-screen error label.
• Irregular timeline: establish a consistent positive step and aggregate duplicate observations deliberately. Missing dates and measured zero values are different inputs.
• Too little seasonal history: supply at least two complete cycles for the requested period. More data may improve estimation, but do not invent missing observations merely to satisfy a count.
• Invalid options: use the stated integer period and option codes. Fractional coercions of completion/aggregation/selectors are not a supported shortcut. A negative or fractional period is rejected by the pinned wrapper.
• Unexpected forecast: verify the argument order, fixed references, target units and explicit period before interpreting the fitted model. Training fit alone cannot establish future reliability.

The Help/source discrepancies above remain unresolved at the installed-product level. No invalid or potentially unsafe case was executed to manufacture an error table.

The contract uses Collabora’s English Help archived on October 17, 2026, scoped to Office 26.04, and the co-26.04 implementation at commit c1013fad0d38683630b48c01b6d5208b8d81181e, retrieved October 19. The latest Help URLs are mutable; the archived bytes establish what was reviewed. A later unsuccessful Help fetch is not fresh verification. First introduction and last functioning installed build are not established. Desktop, Online and mobile editions may differ.

The example calculations were checked using a bounded extraction of the pinned source, an independently written mathematical implementation, full optimizer-state comparisons and a separate arithmetic review. That establishes source-model expectations on the stated fixtures, not vendor certification or identical behavior in every build. These example calculations did not execute formulas in a Collabora application; no native result or screenshot was obtained. No installed RNG or recalculation behavior was tested. Parser/coercion behavior, arrays/spills, calendars, unsupported boundaries and exact displayed error labels remain unverified. The guide is a documentation-qualified tutorial; it is not a claim of native product testing.

• FORECAST.ETS.ADD.
• FORECAST.ETS.MULT.
• FORECAST.ETS.PI.MULT.
• FORECAST.ETS.STAT.ADD.
• FORECAST.ETS.STAT.MULT.
• FORECAST.ETS.SEASONALITY.
• Linear FORECAST.
• Collabora official function Help, reviewed from the archived snapshot.
• Pinned Collabora implementation, including preprocessing, fitting and worksheet dispatch.