Trend funtion

[vijaya]

Ok

I have data like this:

A B A B A B A B
1 10 2 20 3 30 4 ?

The trend formula is =trend (known y, [known x],new x)

Generally we select the known y values which are in continuous cells, and
then select known x values, after which for a new x value – y-values could be
estimated.

But is there any way that this could be done by selecting y and x – values
which are in non-contiguous cells( like values if present in alternative
cells)

Is there a way to program:
1.Collect all the values present in alternate cells starting from say A2 (
these are my y-values)
2. Next collect all the values present in alternate cells starting from say
A3 (these will be x values)
3. Now can I use the trend function for forecasting??

Is this a way possible..

[Toppers]

Hi,

Try this:

Sub GetTrendData()

Dim xRng As Range, yRng As Range
Dim lastrow As Long, i As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Set yRng = Cells(2, "A")
Set xRng = Cells(3, "A")

For i = 4 To lastrow Step 2
yRng = Union(yRng, Cells(i, "a"))
xRng = Union(xRng, Cells(i + 1, "a"))
Next i

Range("c1") = Application.Trend(yRng, xRng)

End Sub

HTH

"vijaya" wrote:

> Ok
>
> I have data like this:
>
> A B A B A B A B
> 1 10 2 20 3 30 4 ?
>
> The trend formula is =trend (known y, [known x],new x)
>
> Generally we select the known y values which are in continuous cells, and
> then select known x values, after which for a new x value – y-values could be
> estimated.
>
> But is there any way that this could be done by selecting y and x – values
> which are in non-contiguous cells( like values if present in alternative
> cells)
>
> Is there a way to program:
> 1.Collect all the values present in alternate cells starting from say A2 (
> these are my y-values)
> 2. Next collect all the values present in alternate cells starting from say
> A3 (these will be x values)
> 3. Now can I use the trend function for forecasting??
>
> Is this a way possible..
>

[Tom Ogilvy]

Your only taking the trend for A2 (y-val) and A3 (x-val)

if you use Set with the Union function as intended, then you get a #Ref for
the answer:

Maybe you wanted to build arrays like this:

Sub GetTrendData()
Dim lastrow As Long, i As Long
Dim ub As Long, j As Long
Dim yRng(), xRng()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ub = (lastrow - 1) / 2 - 1
Debug.Print ub
ReDim yRng(0 To ub)
ReDim xRng(0 To ub)
j = 0
For i = 2 To lastrow Step 2
yRng(j) = Cells(i, "a")
xRng(j) = Cells(i + 1, "a")
j = j + 1
Next i
For i = LBound(yRng) To UBound(yRng)
Next
Range("c1") = Application.Trend(yRng, xRng)

End Sub

--
Regards,
Tom Ogilvy


"Toppers" <Toppers@discussions.microsoft.com> wrote in message
news:C8B57ADF-6CC5-461A-87AC-F1D0844843AD@microsoft.com...
> Hi,
>
> Try this:
>
> Sub GetTrendData()
>
> Dim xRng As Range, yRng As Range
> Dim lastrow As Long, i As Long
>
> lastrow = Cells(Rows.Count, "A").End(xlUp).Row
>
> Set yRng = Cells(2, "A")
> Set xRng = Cells(3, "A")
>
> For i = 4 To lastrow Step 2
> yRng = Union(yRng, Cells(i, "a"))
> xRng = Union(xRng, Cells(i + 1, "a"))
> Next i
>
> Range("c1") = Application.Trend(yRng, xRng)
>
> End Sub
>
> HTH
>
> "vijaya" wrote:
>
> > Ok
> >
> > I have data like this:
> >
> > A B A B A B A B
> > 1 10 2 20 3 30 4 ?
> >
> > The trend formula is =trend (known y, [known x],new x)
> >
> > Generally we select the known y values which are in continuous cells,
and
> > then select known x values, after which for a new x value - y-values
could be
> > estimated.
> >
> > But is there any way that this could be done by selecting y and x -
values
> > which are in non-contiguous cells( like values if present in alternative
> > cells)
> >
> > Is there a way to program:
> > 1.Collect all the values present in alternate cells starting from say A2
(
> > these are my y-values)
> > 2. Next collect all the values present in alternate cells starting from
say
> > A3 (these will be x values)
> > 3. Now can I use the trend function for forecasting??
> >
> > Is this a way possible..
> >