forecasting and linking to a data range that is fluctuating

L

Laurie

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.
 
H

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

Toppers

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top