And you want to solve for σ. But, you want to be able to update a, b, G, and lambda on the fly and see the new σ that makes LHS = RHS. How? 😊 Obviously you want one cell to capture LHS - RHS so you can solve for the value that sends this cell to 0.
Start off getting stuff to work using Goal Seek (Data > What if Analysis > Goal Seek.
Now, the cool part is getting that to run on demand through code. What can you do? Use the Macro Recorder obviously. Do that and you have this in your pocket :
Range("F12").GoalSeek Goal:=0, ChangingCell:=Range("C12")
Now, the cool part is getting this to run when the cells in C8-C11 are changed. How?
In Excel, do ALT-F11 to open the VBA Editor and then, in the Project Explorer, double click the name of the sheet under Microsoft Excel Objects. You now get a new window called filename.xlsm - SheetNamed (Code). In the dropdown on the left, choose "Worksheet" and now, on the right, choose "Change". This will give you this starting code :
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
All you have to do is :
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Range("C8:C11"))
If Not rng Is Nothing Then
' put your magic code here - what magic? The stuff that copies the numerical value you calculate use a formula for the initial guess into a cell that you tell Goal Seek to vary of course 😊
Put it all together:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Range("C8:C11"))
If Not rng Is Nothing Then
'
' solve Macro
' run goal seek
'
Dim sourceCell As Range
Dim targetCell As Range
' Set the source and target cells
Set sourceCell = Range("D12")
Set targetCell = Range("C12")
' Copy the numeric value from the source cell to the target cell
targetCell.Value = Val(sourceCell.Value)
Range("F12").GoalSeek Goal:=0, ChangingCell:=Range("C12")
End If
End Sub
And you're done :) Enjoy
