Contact
12 programs
Explore the site

Zoho Sheet TREND Function: Predict Multiple Values with a Linear Trend

Statistical Advanced Zoho Sheet

Use the Zoho Sheet TREND function for linear estimates, projected ranges and zero-intercept models, with numerical examples and limitations.

Verification status: See the evidence and test scope described in this guide. How formula examples are checked

Quick answer: Use =TREND(B2:B7;A2:A7;D2:D3) to obtain fitted values at the future x-coordinates in D2:D3, given known response values in B2:B7 and their matching x-values in A2:A7. With the six-week orders dataset below and forecast weeks 7 and 8, the independent least-squares calculations give 10.666666667 and 11.952380952 expected orders. Because TREND returns an array, allow room for the result. These are expected mathematical results, not native Zoho Sheet test outputs. Zoho official TREND reference, checked 2026-10-12.

TREND is useful when you want multiple estimated outputs along a straight-line pattern, such as a monthly planning series or the fitted values for a historical chart. It assumes a linear relationship in the selected inputs; it does not model seasonality or promise future outcomes. A high historical fit is not a substitute for business judgement or uncertainty analysis.

Availability and version history

  • Introduced: The original introduction version and date are not established by the checked sources.
  • Availability: Currently documented in Zoho Sheet’s rolling web-service help; no numbered release or retirement endpoint is established.
  • Scope: English-language web-service documentation. Mobile and offline-client parity is outside the verified scope.
  • Checked: October 14, 2026, against the official TREND reference. Documentation check; not a new native test.

Official syntax, argument order and scope

TREND(y_values; [x_values]; [new_x_values]; [type])
Argument Required? Meaning from the official Zoho help Documented default or limitation
y_values Yes The known dependent numeric results to fit, such as B2:B7. Required; no default.
x_values Optional Known independent numeric inputs paired with y_values, such as A2:A7. Zoho mentions support for more than one variable. Zoho says omission gives an implicit {1,2,3,...} sequence; check exact data dimensions.
new_x_values Optional Input x-value(s) for which you want predicted y-value(s), such as D2:D3. The Zoho article does not explicitly state what output range is selected if omitted. Supply it to avoid relying on another spreadsheet’s default.
type Optional Intercept behaviour: FALSE forces b=0; TRUE, any nonzero value, or omission estimates b in y=mx+b. Omitted means intercept calculated. Zoho inconsistently calls this allow_factor in prose.

The individual help page explicitly describes x_values as supporting multiple variables, but does not thoroughly define the dimensions/order for every multi-predictor layout. The single-predictor examples below avoid relying on undocumented shapes. The semicolons are from Zoho’s English help; regional formula separators can differ. No first-supported software version or universal desktop/mobile/account availability claim is made because the cited reference provides no version matrix. Zoho TREND syntax and argument descriptions, checked 2026-10-12.

Step-by-step: plan weeks seven and eight

Enter this complete sample in a blank Zoho Sheet worksheet; headings are text and all six observations are numeric.

Row A: Week B: Orders D: Future week
1 Week Orders Future week
2 1 3 7
3 2 5 8
4 3 4 —
5 4 8 —
6 5 7 —
7 6 10 —
  1. Select an empty output cell such as E2, leaving E3 empty as well.
  2. Enter =TREND(B2:B7;A2:A7;D2:D3) using the separator accepted in your account.
  3. In the expected mathematical model, the predicted series for weeks 7 and 8 is 10.666666667, then 11.952380952. An actual Zoho dynamic array may spill into adjacent cells; the precise visual output has not been tested.
  4. Format as decimals while evaluating the model. If you must plan whole units, perform a separate, explicitly documented rounding decision instead of claiming the regression naturally returns integers.
  5. Plot the original points and fitted line. Pay attention to data outside the historical range: two projected periods are extrapolations, not observed values.

Where the predictions come from: Ordinary least squares for the observations (1,3), (2,5), (3,4), (4,8), (5,7), (6,10) gives slope 9/7 = 1.285714286 and intercept 5/3 = 1.666666667. Therefore, the expected fitted formula is ŷ = (9/7)×week + 5/3. Week 7 is 32/3 = 10.666666667; week 8 is 251/21 = 11.952380952. Both numbers were checked by independent arithmetic; they are not screenshots or native Zoho outputs. Zoho TREND purpose and syntax, checked 2026-10-12.

Need only one prediction?

For a single future x, =TREND(B2:B7;A2:A7;7) has the same statistical expected value as =FORECAST(7;B2:B7;A2:A7) in this one-predictor scenario. TREND is convenient for an array of future x-values, whereas FORECAST explicitly takes one new_x_value in Zoho’s reference. Do not infer every possible exceptional-case treatment is identical. Zoho TREND and FORECAST, checked 2026-10-12.

Fit a historical series for chart comparison

Provide historical x_values again as the third argument: =TREND(B2:B7;A2:A7;A2:A7). The mathematically expected fitted series is:

Week Actual orders Fitted orders (rounded to 6 decimals) Actual minus fitted
1 3 2.952381 +0.047619
2 5 4.238095 +0.761905
3 4 5.523810 −1.523810
4 8 6.809524 +1.190476
5 7 8.095238 −1.095238
6 10 9.380952 +0.619048

A plot of actual vs fitted orders reveals the reversals in weeks 3 and 5 that a single upward line hides. The residuals add to approximately zero with an estimated intercept; this is a property of the underlying least-squares model, not a Zoho execution claim. Zoho TREND, checked 2026-10-12.

Advanced application: zero-intercept scenario planning

If the modeled response must be zero when the predictor is zero, compare an origin-constrained model:

=TREND(B2:B7;A2:A7;D2:D3;FALSE)

The forced-zero slope is mathematically Σxy / Σx² = 152/91 = 1.670329670; resulting week-seven and week-eight expected values are 11.692307692 and 13.362637363. Compare with 10.666666667 and 11.952380952 from the usual intercept model. The choice changes the line—it is not a formatting option. Zero-intercept forecasting can be sensible when the origin is physically meaningful; it is often inappropriate for time-series where “week zero” was never observed. Zoho’s official type description states that FALSE sets b to 0 and omitted/TRUE fits b. Zoho TREND type argument, checked 2026-10-12.

Use TREND in a rolling planning sheet

Keep A2:A7 for known weeks and B2:B7 for known orders. Build the next planned week numbers in a separate D range and point new_x_values at it. When new actual observations arrive, extend both the x and y ranges together and review the forecast horizon. Explicit ranges avoid the silent assumption that skipped weeks are consecutive observations. Do not copy an existing array’s spill cells by hand; edit the source formula and allow the array to update.

For planning accuracy, track at least the last several forecast errors and compare them with a simple baseline, such as the prior week’s result. A fitted trend may look compelling in-sample and still be worse than a naive prediction when demand is seasonal or changes abruptly. Zoho TREND documentation, checked 2026-10-12.

Why LINEST, RSQ and STEYX still matter

TREND estimates y-values but does not itself provide a confidence interval, causal explanation or guarantees about forecast quality. For this six-point model, independent calculations give R² ≈ 0.830485304 and residual standard error ≈ 1.214985793 orders. The first describes how much variation was explained within the fit; the second reports the typical residual scale under the regression standard-error formula. These are useful diagnostics, not fixed future prediction limits.

Use LINEST when you need the fitted coefficients and additional regression diagnostics, RSQ for the squared linear relationship, and STEYX for residual standard error. FORECAST is suitable for a single specified x-coordinate.

Troubleshooting, errors and edge cases

Symptom or risk What to check
#NAME! Check the spelling TREND, separators and whether a workbook-defined name is invalid. Zoho documents these as general error explanations.
#VALUE! Check numeric source cells and the Boolean argument; imported text that looks numeric may need cleaning. Exact mixed-data coercion was not established by Zoho’s TREND article.
#REF! Repair references to moved/deleted ranges. Zoho documents broken references generally.
#N/A! or incomplete array Confirm that each known x has exactly one matching y, ensure the future predictor shape makes sense, and examine available spill space. The function page gives a generic #N/A! description; do not infer a confirmed specific trigger.
#SPILL! Clear adjacent output cells or move the formula to an open range. Zoho’s dynamic-array announcement explicitly documents obstruction handling.
Unexpected forecast Make sure B2:B7 is dependent y and A2:A7 is independent x. Verify D2:D3 contains numeric weeks 7, 8 rather than formatted text.
Only one value appears Confirm whether the requested future range has one value, whether implicit-intersection behaviour is at work, and whether the sheet supports dynamic spilling.
High apparent precision Display fewer decimals and assess real measurement uncertainty; numeric precision does not imply forecast certainty.

Exceptional inputs to validate natively: blank paired positions, text, logical values, errors inside arrays, duplicate/constant x-values, several predictor columns, and omitted new_x_values. Zoho’s TREND article is not a complete edge-case specification, so this guide intentionally does not invent one. The official reference states only generic #N/A!, #NAME!, #VALUE!, #REF! explanations. Zoho TREND errors and Zoho array spilling, checked 2026-10-12.

Zoho vs Excel and Google Sheets: important distinctions

Product Official syntax checked Documented distinction
Zoho Sheet TREND(y_values; [x_values]; [new_x_values]; [type]) The fourth argument is named type; the article does not explicitly state the default for omitted new_x_values.
Microsoft Excel TREND(known_y's, [known_x's], [new_x's], [const]) Microsoft documents the new-x omission case and distinguishes modern dynamic spilling from older Ctrl+Shift+Enter array entry. Those instructions are Excel-specific.
Google Sheets TREND(known_data_y, [known_data_x], [new_data_x], [b]) Google explicitly documents new_data_x defaulting to known x; an English help sentence mistakenly says “exponential” while displaying linear y=mx+b. Do not copy that typo as a change to TREND’s mathematical purpose.

These references establish what each provider publishes, not proven identical application behaviour. In particular, do not assume identical treatment of missing observations, multi-predictor layouts, locale separators or historical client versions. See Microsoft TREND and Google TREND, both checked 2026-10-12.

Validation: Zoho reference and cross-software references checked 2026-10-12; displayed predictions and regression diagnostics independently calculated from six explicit input pairs; native Zoho execution = NOT_RUN; actual output spill orientation, omitted-new-x behaviour and multi-predictor shapes = NOT_VERIFIED.