forecasting and linking to a data range that is fluctuating

[Guest]

I'm using the forecast function. My formula is =forecast(a3, D220,E2:E20).
My problem is that when I update my known x and y's the data range changes
(i.e. ends at row 24 instead of 20). Is there a way to reference my data
range in my forecast function to cells in column D and E that have numbers in
them so that the forecast formula automatically changes to include all of my
known x and y's? I can't just reference a really large area (i.e.
=forecast(a3,D2:200,E2:E200) because my forecast is linked to other tables
and formulas that can't handle having empty cells.

Thanks for any advice you have.

 

[Hans Knudsen]

Create a named formula. Insert, Name, Define - in the Names in workbook field write for example known_ys and in the Refers to field
enter the following formula:
=OFFSET($D$2,0,0,COUNTA(D)-1,1). Create a new named formula and call it for example known_xs, formula:
=OFFSET($E$2,0,0,COUNTA(E:E)-1,1).
(assume you have a heading in row 1)
Now you can use:
FORECAST(A3;known_ys;known_xs)

Regards
Hans

 

[Guest]

Try:

=FORECAST(A3,Dcol,Ecol)

Where DCol (and ECol) are dynamic Named Ranges defined (in the "Refers to:"
entry of the Define Name form) as:

=OFFSET(Sheet3!$D$1,0,0,COUNTA(Sheet3!$D:$D),1)

and (for Ecol)

=OFFSET(Sheet3!$E$1,0,0,COUNTA(Sheet3!$E:$E),1)

Change sheet (and names) as required.

HTH

 

[Guest]

Hans has the correct format as your data starts in row 2 not 1 as per my reply.