Excel FORECAST on pre-determined number.

[S.H.A.D.O.]

Good afternoon,

I have a two ranges of data in cells E15:K? And M15:S?
I have a figure in cell A1 that increases by one when new data is added to the bottom of the ranges above.
The code below works and does exactly what I want it to do...

Sub FORECAST_Next_Numbers()
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
   
    Range("E10").Resize(1, 7).Formula = _
            "=FORECAST(INDIRECT(""B"" & $A1+15),OFFSET(E15,,,$A1,1),OFFSET($B15,,,$A1,1))"
           
    Range("M10").Resize(1, 7).Formula = _
            "=FORECAST(INDIRECT(""B"" & $A1+15),OFFSET(M15,,,$A1,1),OFFSET($B15,,,$A1,1))"
           
    Range("E10:S10").Value = Range("E10:S10").Value
    Range("E10:K10,M10:S10").NumberFormat = "0"
   
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

I want to be able to forecast from the bottom of the data up by the figure in cell B1 as opposed to the whole of the data.
So for example, if I had the number 50 in cell B1, I just want it start at the last figure in column E and calculate the FORECAST up on the last 50 numbers.

I hope this makes sense.

Thanks in advance.

 

[pbornemeier]

I could not fully understand what you want to do, but I think that a dynamic named range will get you to where you want to be

Define a range named LastE using this formula
=OFFSET(Sheet1!$A$1,Sheet1!$A$1-Sheet1!$B$1,4,Sheet1!$B$1,1)

It will return a column E range (An offset of 4 columns from the A1 reference = column E)
That ends at the row specified in cell A1
And starts such that the number of rows included is the value in B1

Define LastF as
=OFFSET(Sheet1!$A$1,Sheet1!$A$1-Sheet1!$B$1,5,Sheet1!$B$1,1)
to get a similar range in column F

 

[pbornemeier]

Rereading your post, you can modify the second input of your FORECAST formulas as follows

OFFSET(E15,$A$1-$B$1,0,$B$1,1)

will refer to the cells in column E starting at row A1-B1, for a height of B1 cells

and similarly modify the third input.

 

[S.H.A.D.O.]

Thanks for the replies Phil, it is appreciated.

I basically want to start at the last cell of data in column E for example and include 50 cells (figure in cell B1) up from that to work out the FORECAST on.

I tried the formula from your second post with data where I knew what the answer should be and it didn't give me the correct figures, strange!

Thanks again.

 

[S.H.A.D.O.]

Perhaps this needs something like a Range("E15", .Range("E"& .Rows.Count).End(xlUp).Offset(1, 1))?

 

[S.H.A.D.O.]

Has anyone got any ideas or suggestions please?

 

[pbornemeier]

Is the value in A1 the last populated row?
What data is in column B?

 

[Kenneth Hobson]

Multiply your end row to offset to find the beginning row by -1? Of course there must be that many rows. e.g. Last row is 10. Row= -1 would not be a valid beginning row number.

I would use a Worksheet Change event for beginning and end rows. I would then just put the result rather than a formula. If you need a more detailed example, I can work one up.

The dynamic named range approach might be doable as well. Named range a Trend() might be: =TREND(ys,xs,5)

Here is what I did with Trend() once.

Sub Test_eInterp()
  '=einterp($B$2:$B$10,$A$2:$A$10,A2)
  MsgBox eInterp([B2:B10], [A2:A10], 5)(1)
End Sub

Function eInterp(Known_ys As Variant, Known_xs As Variant, _
  Optional New_xs As Variant = 0) As Variant
  Dim d As Variant, y() As Variant, x() As Variant
  y() = WorksheetFunction.Transpose(Known_ys)
  x() = WorksheetFunction.Transpose(Known_xs)
  'New_xs = Array(New_xs)
  d = WorksheetFunction.Trend(Known_ys, Known_xs, Array(New_xs))
  If d(1) >= Known_ys(1) And d(1) <= Known_ys(2) Then eInterp = d
  eInterp = d
End Function

 

[S.H.A.D.O.]

Thanks for the reply Phil,

The value in A1 is the number of rows that has data at present.
The value in cell B1 could be anything I enter, let's assume I have 50 in cell B1.

So for example, if the figure in A1 was 2,285 (calculated), the last row with data would be 2,299 (that is row 15 to row 2299).

For FORECAST purposes I want to find the cell with data in the last row (column E for example), select that cell and also the 49 cells above it which makes 50 and is the figure in cell B1.

I basically want to be able to FORECAST from the last cell in the column up by the figure in cell B1.

I hope this explains it in a bit more detail.

Thanks for the reply Kenneth,

I didn't really want to use the Worksheet Change event or the dynamic named range approach as I think that this can be achieved with adapting my initial code, but unfortunately I just can't seem to get my head round how to do this.

Thanks again to BOTH of you for your time and input.

 

[S.H.A.D.O.]

I don't know if this helps in trying to explain what I am trying to do but I came up with the following.
It's the FORECAST bit that needs calculating that I can't figure out.
Obviously there must be a way to do it without selecting the cells too.

Sub Number_In_B1()
    Dim lstrw As Long
    lstrw = Cells(Rows.Count, "E").End(xlUp).Row
    Range("E" & lstrw - Range("B1") + 1 & ":E" & lstrw).Select
    
    With Selection
        Range("E10").Resize(1, 7).Formula = _
            "=FORECAST(?????????????????????)"
    End With
End Sub

Thanks in advance.