I'm using the forecast function. My formula is =forecast(a3, D2:D20,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:D: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.

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: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



"Laurie" skrev i en meddelelse ...
I'm using the forecast function. My formula is =forecast(a3, D2:D20,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:D: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.

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

"Laurie" wrote:

I'm using the forecast function. My formula is =forecast(a3, D2:D20,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:D: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 has the correct format as your data starts in row 2 not 1 as per my reply.

"Toppers" wrote:

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

"Laurie" wrote:

I'm using the forecast function. My formula is =forecast(a3, D2:D20,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:D: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.