In this page I have included some advanced issues that can be solved with the UDF functions. In the case below you can use ideas of a slope and intercept to develop a dynamic goal seek.

.

Sub experiment() debug2 = False time_saving_mode = False If time_saving_mode Then Application.ScreenUpdating = False Else Application.ScreenUpdating = True End If If time_saving_mode = False Then Application.StatusBar = "......................................................................." test6 = DoEvents End If Range("start_time") = Time Range("prob_scenario") = 1 Count = 1 Sheets("Summary").Calculate ' Make sure have recent parameters low_price = Range("low_start") ' Can change these high_price = Range("high_start") ' Can change increment = (high_price - low_price) / 5 ' Divisor can change speed If time_saving_mode = False Then Application.StatusBar = "Part 1: Find Slope and Intercept ...................................................." test6 = DoEvents End If Count = 1 iteration = 0 ' Counts iteration for output For ppa_price = low_price To high_price Step increment iteration = iteration + 1 find_npv ' Call routine to find NPV; need to calculate npv_output(iteration) = npv_result ' Store Iteration in an array ppa_output(iteration) = ppa_price Count = Count + 1 If npv_result > 0 Then Exit For ' When hit positive NPV stop (start with low price) Next ppa_price ' Now go back and get numbers between zero If iteration > 1 Then ' After exit, make low and high for slope low_ppa = ppa_output(iteration - 1) low_npv = npv_output(iteration - 1) End If high_ppa = ppa_output(iteration) high_npv = npv_output(iteration) ' call find npv ' Y is the NPV ' X is the PPA Price ' Equation: NPV = A + B x Price ' B = Slope = (y1 - y2)/(x1 - x2) If debug2 Then MsgBox " High PPA " & high_ppa & " High NPV " & Format(high_npv, "###,###.00") & " Low PPA " & low_ppa & " Low NPV " & Format(low_npv, "###,###.00") b = (high_npv - low_npv) / (high_ppa - low_ppa) ' high_npv = a + b x high_ppa ' a = high_npv - b x high_ppa a = high_npv - b * high_ppa If debug2 Then Range("a") = a Range("b") = b End If ' MsgBox " Slope " & a & " Intercept " & b ' now find zero npv point ' NPV = a + b * ppa_price ' 0 = a + b * ppa_price ' -a = b * ppa_price ' - a/b = ppa_price If b <> 0 Then ppa_price_base = -a / b ' Theoretical zero NPV from Line ppa_price = ppa_price_base find_npv ' Find zero NPV from line If debug2 Then MsgBox " Trying from Straight Line " & Chr(13) & _ " PPA Price from Line " & Format(ppa_price, "00.00") & " NPV Computed " & Format(npv_result, "0.00") & " Increment " & increment ' Now re-do a and b iteration = iteration / 10 ' Set divisor for slope calculation -- the high and low bound re_start_iteration: ' Try new line with more precise slope and intercept ppa_price = ppa_price_base + iteration / 2 ' Last ppa price plus iteartion find_npv high_ppa = ppa_price high_npv = npv_result ppa_price = ppa_price_base - iteration / 2 ' start of iteration find_npv low_ppa = ppa_price low_npv = npv_result ' MsgBox " High PPA " & high_ppa & " Low PPA " & low_ppa ' Now re-compute the slope and intercept If (high_ppa - low_ppa) <> 0 Then b = (high_npv - low_npv) / (high_ppa - low_ppa) a = high_npv - b * high_ppa If debug2 Then Range("a") = a Range("b") = b End If If b <> 0 Then ppa_price_base = -a / b ppa_price = ppa_price_base find_npv ' Get the zero NPV value from the line If debug2 Then MsgBox " Trying from Straight Line " & Chr(13) & _ " PPA Price from Line " & Format(ppa_price, "00.00") & " NPV Computed " & Format(npv_result, "0.00") & " Increment " & increment iteration = iteration / 8 Count = Count + 1 If Abs(npv_result) < 100 Then ' Put in criteria If debug2 Then MsgBox " Final NPV " & Chr(13) & _ " NPV " & Format(npv_result, "###,###.00") & " PPA Price " & Format(ppa_price, "00.00") GoTo finish_sub End If If Count > 180 Then Exit Sub If time_saving_mode = False Then Application.StatusBar = "Part 2 ................................ Iteration " & Count & " NPV " & Format(npv_result, "###,##.00") test6 = DoEvents End If GoTo re_start_iteration: finish_sub: Range("ppa_price").Value = ppa_price Range("ppa_price").Calculate Range("end_time") = Time Range("iterations") = Count Sheets("Summary").Calculate Application.StatusBar = False test6 = DoEvents End Sub