VBA 2010: Coefficient trend lines to add in a code

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
984
Office Version
  1. 2010
Platform
  1. Windows
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.
VBA Code:
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi MonteCarlo,
This bit helps you making your Range flexible:
VBA Code:
    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
 
Upvote 0
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.
 
Upvote 0
Rijnsent you are right your line make this code more flexible, but still the sumproduct it is not flexible at all,
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top