Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
So you have a formula x = b^(d+1) - N(b-1) - 1 that you need to solve to get a working value of b for a known x, d and N? What should the formula equal (ie, what is x)? – DMA57361♦ Feb 5 '11 at 23:37
The original formula is N = b + b^2 .. b^d and I need to find b. The above formula, when used correctly in Excel gives the correct value of b. – efficiencyIsBliss Feb 5 '11 at 23:42
I should add that the formula that DMA57361 wrote reduces to the one I wrote. – efficiencyIsBliss Feb 5 '11 at 23:45
In which case you're saying the Excel formula should equal zero? If that's so, I think your formula may be incorrect -- when used with known values or N,d and b (calculated from the original formula) the value returned is not zero. Maybe 0 = b^(d+1) - N(b-1) - b instead? However, I wonder if there's some worth of first asking on Math.StackExchange how to get b from the original formula for a known N and d, there might be a more mathematically direct approach than Excel goal seek (which is just an iterative search). – DMA57361♦ Feb 6 '11 at 0:18
I always like to use WolframAlpha to solve this kind of equations. For example, 62 = summation n=1 to 5 b^n – wilson Feb 7 '11 at 6:55

2 Answers

up vote 1 down vote

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
share|improve this answer
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.