TREND Function Requires Known_x's if New_x's Are Specified

Last reviewed: November 3, 1994
Article ID: Q76671

SUMMARY

Microsoft Excel version 2.1 returns a #VALUE! error if the Known_x's argument is omitted from the TREND function and New_x's is included. Excel for Windows version 3.0 and all versions of Excel for OS/2 allow you to omit Known_x's while including New_x's.

MORE INFORMATION

The syntax for TREND is:

   =TREND(Known_y's,Known_x's,New_x's)

Known_x's can be omitted as long as New_x's is omitted as well. If Known_x's is omitted, the default values of {1,2,3...} or {1;2;3...} are used.

Example

Enter the following data into a new worksheet:

   A1:     1       B1: 1000
   A2:     2       B2: 2000
   A3:     3       B3: 3000
   A4:     4       B4: 4000
   A5:     5       B5: {=TREND(B1:B4,,A5:A6)}
   A6:     6       B6: {=TREND(B1:B4,,A5:A6)}

To enter the formula in cells B5:B6, first select both cells, then type the formula (without the curly brackets). Press CTRL+SHIFT+ENTER to enter the formula as an array formula.

In Excel version 2.1, a #VALUE! error will result in B5:B6. The values 5000 and 6000 are returned in Excel version 3.0.

Solution

To correct the problem in version 2.1, simply include Known_x's. The correct formula for the above example would be:

   {=TREND(B1:B4,A1:A4,A5:A6)}

KBCategory: kbother
KBSubcategory:
Additional reference words: 2.10 2.10c 2.10d 3.00 2.20 2.21
REFERENCES
"Microsoft Excel Functions and Macros," version 2.1, pages 117-119
"Microsoft Excel Function Reference," version 3.0, pages 238-239
KBCategory: kbother
KBSubcategory:
Additional reference words: 2.10 2.10c 2.10d 3.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 3, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.