3

I have to use the following formula:

=POWER(b,(d+1))-N*(b-1)-1

I need to find b, so every time I run this I have to select the values for d and N, and then goal seek to make b the calculated value. The problem is, I have about 1500 entries to calculate b for. Is there a way to automate the calculation? Like the way we drag the input values for simpler functions?

Lance Roberts
  • 8,837
efficiencyIsBliss
  • 2,439

2 Answers2

3

First, Your last '1' needs to be a 'b', as some others brought up.

Second, I found Goal Seek really doesn't work too well (if you sub the 'b' in), because 1 will always solve this equation. This means that Goal Seek won't really solve it, since there's no way of constraining a minimum on the value, BUT if you want to just automate Goal Seeker, there is this expensive add-in.

Third, The VBA way to run GoalSeek is

Sheets("Sheet1").Range("A1").GoalSeek Goal:=0, ChangingCell:=Sheets("Sheet1").Range("B1")

where here "A1" would be the cell with the formula in it and "B1" would be the empty cell that would end up with the 'b' value. So you could create a loop for it and automate that way.

Fourth, The best way is to use Solver in VBA. Enable the Solver Add-In, then in VBA enable the Solver reference. Use the following code as an example, where the D column has the formula, the C column is where 'b' will end up, and $H$1 is the value 2, so that we keep it from solving it with b=1:

Public Sub SolveGeometric()

Dim i As Integer

    For i = 2 To 3
        SolverReset
        SolverOk SetCell:="$D$" & i, MaxMinVal:=3, ValueOf:="0", _
            ByChange:="$C$" & i
        SolverAdd CellRef:="$C$" & i, Relation:=4  'keep it an integer
        SolverAdd CellRef:="$C$" & i, Relation:=3, FormulaText:="$H$1"
        ' $H$1 = 2, so that it won't find 1 which always solves the equation
        SolverSolve userFinish:=True
    Next i

End Sub
Lance Roberts
  • 8,837
0

I suggest you look up what a "geometric series" is so that you can check the formula you are trying to solve.

One idea that springs to mind (and may or may not work efficeintly or at all) to deal with solving your problem is to use the "solver" add-in in Excel. I would have columns of values for N, d, and initial b. one for the calculated N values and one for the square of the difference between the calculated and desired N values.

Sum the squares of the differences in N values and use solver to set it to a value of 0 by changing the cells in the column of b values.

Neal
  • 8,838