Trend function different length rows

[Waboku]

Hello,

Please consider helping me with my problem. I have different length rows I would like to analyze with the trend function but I don't know how. For example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Trend 1[/TD]
[TD]Trend 2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]3.5[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8[/TD]
[TD]11[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4.5[/TD]
[TD]4.8[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would like to be able to use trend to produce the results in columns A & B. The function I used to get the results in A2 & B2 is below.

Cell A2:
TREND(C2:E2,C1:E1,F1)

Cell B2 (for the day after the 1st trend):
TREND(C2:E2,C1:E1,G1)

Y values equal current row from column C to the right until last cell that contains a number.

X Values equal the cells in row 1 above the Y Values.

New X Value for trend 1 equals the cell to the right of the rightmost X value. For trend 2 the new X value equals 2 cells to the right of the rightmost X value. Otherwise trend 2 is the same as trend 1.

If someone knows how to do this and doesn't mind helping me I would greatly appreciate it. If I need to provide more information please let me know as well. Thanks again.

 

[DRSteele]

This uses function OFFSET to achieve the objective. Keep in mind that its use in an extensive manner can slow the recalculation of the workbook.

Copy A2 and B2 downwards to each relevant row.

	A	B	C	D	E	F	G	H

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Trend 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] "]Trend 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , align: right"]6[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]3.5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]4.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]4.8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet16​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=TREND(OFFSET($C2,,,,COUNT($C2:$H2)),OFFSET($C$1,,,,COUNT($C2:$H2)),INDEX($C$1:$H$1,,COUNT($C2:$H2)+1))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=TREND(OFFSET($C2,,,,COUNT($C2:$H2)),OFFSET($C$1,,,,COUNT($C2:$H2)),INDEX($C$1:$H$1,,COUNT($C2:$H2)+2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Waboku]

Thank you very much. That worked.

 

[DRSteele]

Good show! You're welcome.