+ Reply to Thread
Results 1 to 4 of 4

forecasting and linking to a data range that is fluctuating

  1. #1
    Laurie
    Guest

    forecasting and linking to a data range that is fluctuating

    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.

  2. #2
    Hans Knudsen
    Guest

    Re: forecasting and linking to a data range that is fluctuating

    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" <[email protected]> skrev i en meddelelse news:[email protected]...
    > 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.




  3. #3
    Toppers
    Guest

    RE: forecasting and linking to a data range that is fluctuating

    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.


  4. #4
    Toppers
    Guest

    RE: forecasting and linking to a data range that is fluctuating

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1