Roger, I've made some minor changes to the rest code as follws:
Sub test()
Dim b As Range
a = Selection
Set b = Selection.Cells(1, 1)
n = UBound(a)
For irow = 0 To n - 1
b.Offset(irow, 0).Formula = a(n - irow, 1)
b.Offset(irow, 1).Formula = a(n - irow, 2)
b.Offset(irow...
Here's another approach you may find interesting.
Sub test()
Dim b As Range
a = Selection
Set b = Selection.Cells(1, 1)
n = UBound(a)
For irow = 0 To n - 1
b.Offset(irow, 0).Formula = a(n - irow, 1)
Next
End Sub
The macro button is an ActiveX control. Use the menu item View:Toolbars:Control Toolbox and ensure that the Control Toolbox is checked. Click the View Code button on the tool bar which will open VBA editor showing the event procedure triggered by clicking the toggle control as follows:
Private...
I've provided a link to a sample workbook. It has a toggle button that will change values and unit symbols based on the state of the toggle button. Cells of interest have been identified using a range name. These include units values and units symbols. The example is simple showing just...
Here's a simple template that I use to match staff hour to project hours graphically. Right click in the planning area to toggle the shading and change to hours totalshttp://files.engineering.com/getfile.aspx?folder=9cb5f107-5a57-43a4-9c30-e6255f30ea71&file=StaffPlanning.xlt
Here's a little snippet that makes use of the keypress event
Dim LastKeyAscii As Variant
Dim LastTextBox1 As String
Private Sub TextBox1_GotFocus()
LastKeyAscii = 0
End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 46 And (LastKeyAscii = KeyAscii)...
You can use the intersect method to tell if a changing cell in one of interest. For example,
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,B1")) Is Nothing Then Exit Sub
Range("C1").Value = Range("A1").Value + Range("B1").Value
End Sub
There are limits to what LINEST can do. Looking at the add trendline feature avalable in Excel charting you can see that only polynomials of order 6 can be fitted. This may be a hint that when regressions having more than 7 coefficients in the approximating equation, there may be computational...
If you include the power terms you will no longer have a linear system. Techniques for non-linear regression need to be applied. The exception, as jghrist notes, is when all terms are power terms and you can take the logarithm and "linearize" the equation. For non-linear fitting it may be...
To include the products x1*x2, x1^2*x2, x1^3*x2 as well, use another variation as follows:
=LINEST(Y1:Y10,B1:B10^{1,2,3,0,0,0,1,2,3}*C1:C10^{0,0,0,1,2,3,1,1,1},TRUE,TRUE)
