Run a simulation several times with different parameters and store values

Consider a spreadsheet which performs some computations based on a fixed value (in the rxample below, D3) and is iterative. E.g.

D3: 4

B3: 12 
B4: 58 (=B3*$D$3+10)
B5: 242 (=B4*$D$3+10)
B6: 978 (=B5*$D$3+10)

Total = 1290 (=sum(B2:B5))

Now, suppose I wanted to try out different values of D3 (let's call this P) and store the different totals I get, i.e.

P    Total
4 1290
5 2252
6 3618
7 5460

How would I do this with Excel? A macro? Please note that the above example is a simplified version of the real thing. It should be clear that I need to compute B3-B6 so I can compute the sum.

Update:

Each computation requires several columns.开发者_高级运维 E.g. we would use values on B3,B4, .. and C3,C4, ... .

---

A macro can do this. If B7 contains the sum formula, try this

Sub RunSimulation()
     Dim p as long
     for p = 4 to 7
         Range("D3")=p
         Debug.Print Range("B7")
         Range("L" & (p-1)) = Range("B7").Value
     next
End Sub

EDIT: added a line for storing the results, as requested.

If you don't want to enter the sum formula in your sheet, you can calculate the total in VBA either:

Dim total as Long 
Dim row as long
total = 0
for row = 2 to 5
    total = total + Range("B" & row)
Next
Debug.Print total

(Use Double instead of Long for total if you are dealing with floating point numbers.)

---

Usually it is done in following manner:

    A     B         C         D
1   x     tmp1      tmp2      Total
2   3     $A2+10    $B2*10+10 $C2*$B2   The formulae are just for example.
3   $A2+1 $A3+10    $B3*10+10 $C3*$B3
4   $A3+1 $A4+10    $B4*10+10 $C4*$B4

Excel has capabilities to automatically increment indices in formulae.