VBA 2010: Coefficient trend lines to add in a code

[montecarlo2012]

Hello, Everyone Hoping all be safe.

I have a complex task, I don’t Know how to resolve this.

EXCELENT code provided by StephenCrump Well-known Member Worked just perfectly Beautiful, for the objective I ask for.

Now, the code, loop the TREND formula and compare results with the input data and count them, until here perfect,

So, what is my problem?

In this moment I need no just the trend formula, I need the six coefficient trend lines to plugging in this code (“no clue” expecially with the Linest”, Sorry)

But, EACH formula must display the results in separate sheets.
Trend on sheet 1, Exponential Sheet 2, Linear Sheet 3, Logarithmic s4,
Polynomial in 5, power in 6 and moving average on 7.
And one more thing, I would like also to change a line (already tryed, do not work)
Set rngData = Range("B3:G2720") for something like: Cells(Rows.Count, "A").End(xlUp).Row
in order to read Just the array (dynamic) that is active at the moment.

Thank you for reading this.

Sub Monte_carlo_2012()
         Dim rngStart As Range, rngData As Range
         Dim Diff1 As Long, Diff2 As Long, NoRows As Long, NoCols As Long, i As Long
         Dim s As String
                  Set rngData = Range("B3:G2720") 'Cells(Rows.Count, "A").End(xlUp).Row
                           NoRows = rngData.Rows.Count
                           NoCols = rngData.Columns.Count
                           Diff1 = 8: Diff2 = 35
                  Set rngStart = Range("I3").Resize(, NoCols)
                           For i = Diff1 To Diff2
                                    With rngStart.Offset(, (NoCols + 1) * (i - Diff1)).Resize(NoRows - i)
                                                         .Rows(0).Formula = "=SUMPRODUCT(--(" & rngData.Resize(NoRows - i, 1).Address(0, 0) & "=" & .Columns(1).Address(0, 0) & "))"
                                                         .Rows(0).Font.Bold = True
                                                         .Formula = "=TRUNC(TREND(" & rngData.Resize(i + 1, 1).Address(0, 0) & "))"
                                                         s = .Cells(1, 1).Address(0, 0)
                                             With .FormatConditions
                                                      .Delete
                                                      .Add Type:=xlExpression, Formula1:="=" & rngData(1, 1).Address(0, 0) & "=" & s
                                                      .Item(1).Interior.Color = vbYellow
                                             End With
                                    End With
                           Next i
End Sub

 

[Rijnsent]

Hi MonteCarlo,
This bit helps you making your Range flexible:

    Set Sht = Worksheets("Input Sheet")
    Set rngData = Sht.Range("B3:G" & Sht.Cells(Rows.Count, "A").End(xlUp).Row)

You describe you "have a complex task" and "the code is excellent", care to clarify what you're trying to achieve? I ran the code on a random dataset and it just gives a whole lots of columns of data, it looks like you are trying to forecast the number for today based on the numbers in the past x days (x from 8 to 35 in your code) and see which method works best? For that, you might want to start by not including the data for that last day. And secondly: I don't know what you do with the SUMPRODUCT formulas, but do keep in mind that if that shows e.g. 6 (matches), the number of lines in that column is also important, not the number on its own. And the other methods would also need to forecast the value for the next day based on the various other forecast types? See this article on their formulas: Excel trendline types, equations and formulas
Cheers,
Koen

 

[montecarlo2012]

Rijnsent (Well-known Member) ►Thank you so much◄ for your Input, In this moment I am testing the real capacity of the trend lines, I design this in order to know which line is the best under what circumstance also, so far is what really going on. tonight when I get home I will check your lines, again thank you so much, I like the people who like complex situations like you and the person who give me the code in the first place Mr. StephenCrump. There are so many Remarkable people here, this is a real GREAT forum.
Thank you Rijnsent for answer me and care about this subject.

 

[montecarlo2012]

Rijnsent you are right your line make this code more flexible, but still the sumproduct it is not flexible at all,