Linest Poly Array using data horizontally

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GregJ - 11 Nov 2007 18:19 GMT

All of our metrics store data horizontally, meaning Months (x) are
stored by column and data series (y) are stored in rows below the
months...

Mo: 1 -- 2 -- 3--4 -- 5
Data 31 -- 33 -- 36 -- 38 -- 39

When I apply any Order Poly Linest() array on the data set, I get
errors. Tranpose the data vertically and the Linest() array works.

Any advice?

Reply to this Message

Bernard Liengme - 11 Nov 2007 18:52 GMT

I expect you had something like =LINEST(B3:K3,B2:K2^{1,2}) but the comma
between 1 and 2 is appropriate for vertical (columnar) data. For horizontal
data (data in rows) you need a semicolon (I hope you are using an English
version of XL!!) as in
=LINEST(B3:K3,B2:K2^{1;2})

This is taken for XL's Help:
............ quote
The format of array constants
Array constants are enclosed in braces ( { } ).

Separate values in different columns with commas (,). For example, to
represent the values 10, 20, 30, and 40, enter {10,20,30,40}. This array
constant is known as a 1-by-4 array and is equivalent to a 1-row-by-4-column
reference.

Separate values in different rows with semicolons (;). For example, to
represent the values 10, 20, 30, and 40 in one row and 50, 60, 70, and 80 in
the row immediately below, you would enter a 2-by-4 array constant:
{10,20,30,40;50,60,70,80}.

...........end quote

best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> All of our metrics store data horizontally, meaning Months (x) are
> stored by column and data series (y) are stored in rows below the
[quoted text clipped - 7 lines]
>
> Any advice?

Reply to this Message