# Goal Seek - Multiple Results at Once

#### wardy0601

##### Board Regular
Hi All,

Just wanted to know whether there is a way to do a goal seek to find some interest rate results (% of rates to be set at various intervals) that have to be input manually as the sheet won't allow a non circular calculation to be automated - over say 20 cells (outputs) at once as opposed to having to do them all manually to find each output separately?

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Eric W

##### MrExcel MVP
Sure. Two ideas: if the formula can be inverted algebraically, then you can enter the formula in a cell and drag down. Second, if the formula can't be inverted easily (and I know may interest formulas fit into that category), then you can write a simple VBA script to loop through your list and output all the answers in a column.

For example:

ABCDE
151006.6E-07
21002001100.019245
30.1486981200.037136
41300.053873
51400.06961
61500.084472
71600.09856
81700.111961
91800.124746
101900.136974
112000.148698

</tbody>
Sheet1

Worksheet Formulas
CellFormula
B2=A2*(1+A3)^A1

</tbody>

<tbody>
</tbody>

B2 contains a simple formula to calculate FV given number of years in A1, PV in A2, and interest in A3. Now to figure out what interest rate is needed to achieve a FV of 100-200 in increments of 10, you could the the Goal Seek 11 times. Or this macro does it automatically:

Code:
``````Sub Macro1()
Dim r as long, x as long
'
r = 1
For x = 100 To 200 Step 10
Range("B2").GoalSeek Goal:=x, ChangingCell:=Range("A3")
Cells(r, "D") = x
Cells(r, "E") = Range("A3")
r = r + 1
Next x
End Sub``````

This puts the desired FVs and corresponding interest rates in D:E as shown. Let me know if you need help adapting this to your requirements.

#### wardy0601

##### Board Regular
Thanks Eric,

Played around with it and it worked well in VBA!

What program do you use to extract screen/formula shots in Excel 2016 as you have done in your post above?

#### Eric W

##### MrExcel MVP

I use the HTML Maker tool, click the link in my signature to see how to use it.

Replies
8
Views
386
Replies
1
Views
198
Replies
3
Views
365
Replies
6
Views
440
Replies
3
Views
245

1,195,934
Messages
6,012,383
Members
441,693
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back