# Formula for Goal Seek

#### Odell Beckham

##### New Member
Hi all,

I’m new to this forum but seeking your advice on how I can put a goal seek into a formula as I have to do this for multiple occasions within the same sheet.

Example is that I have my total shares and the shares of a specific shareholder.

A1 (total shares) = sum(A1:A4) = 100

A2 (shareholder 1) = 10

A3 (shareholder 2) = 20

A4 (shareholder 3) = 70

I’m looking to find a formula that will increase the shares shareholder 1 needs to hold so that the shareholder has e.g. 20% of the total shares and shareholder 2 has e.g. 30% of the total shares while shareholder 3 remains with the same amount of shares. Therefore, the total amount of shares needs to be increased by adding additional shares so that shareholder 1 & 2 own the respective shareholding.

Could you kindly help me with this problem?

Thanks

### Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### joeu2004

##### Well-known Member
I’m new to this forum but seeking your advice on how I can put a goal seek into a formula as I have to do this for multiple occasions within the same sheet.

Your problem does not require Goal Seek to solve. And it is difficult to apply Goal Seek to "multiple scenarios" without relying on VBA.

So why are you asking for a Goal Seek solution? A requirement of a class assignment?

A1 (total shares) = sum(A1:A4) = 100

Of course, the SUM formula cannot and should not include A1, if you put the formula into A1.

It is important to show us formulas exactly as they appear in the Formula Bar.

I’m looking to find a formula that will increase the shares shareholder 1 needs to hold so that the shareholder has e.g. 20% of the total shares and shareholder 2 has e.g. 30% of the total shares while shareholder 3 remains with the same amount of shares. Therefore, the total amount of shares needs to be increased by adding additional shares so that shareholder 1 & 2 own the respective shareholding.

Perhaps something like the following. Click on each cell or hover the cursor over each cell to see formulas. I tried to generalize the formulas so that you can increase the number of investors. It could be generalized even more (better?), if necessary.

investor realloc.xlsx
ABCDEF
1Curr Alloc%AllocReqd AllocNew Alloc%Alloc
2Total100140140
3Investor 11010.00%20.00%2820.00%
4Investor 22020.00%30.00%4230.00%
5Investor 37070.00%7050.00%
Sheet1

The key is the calculation of the required total in D2, namely:

=ROUNDUP(E5 / (1 - SUM(D3:D5)), 0)

I use ROUNDUP instead of ROUND in order to ensure that there is sufficient total.

In general, the new allocations are the required percentages of the required total. For example, in E3:

=ROUND(D\$2*D3, 0)

However, the last percentage-based allocation (E4) is calculated differently to ensure that rounding does not affect the total. So it is the total minus the sum of the other rounded allocations (E3) and minus the sum of the fixed allocations (E5), to wit:

=D2 - SUM(E\$3:E3) - SUM(E5:E\$5)

#### joeu2004

##### Well-known Member
Errata, too late to edit....
I tried to generalize the formulas so that you can increase the number of investors.

To that end, the formula in D2 should be:

=ROUNDUP(SUM(E5:E5) / (1 - SUM(D3:D5)), 0)

where SUM(E5:E5) is the sum of all fixed allocations.

#### joeu2004

##### Well-known Member
Sorry for the incessant postings, but I must apologize....
I’m new to this forum but seeking your advice on how I can put a goal seek into a formula as I have to do this for multiple occasions within the same sheet.

Your problem does not require Goal Seek to solve. And it is difficult to apply Goal Seek to "multiple scenarios" without relying on VBA. So why are you asking for a Goal Seek solution?

On second thought, I suspect that ``how I can put a goal seek into a formula`` was intended to mean ``how can I use a formula instead of Goal Seek``, so we are saying the thing. Mea culpa!

Replies
1
Views
388
Replies
14
Views
193
Replies
0
Views
226
Replies
2
Views
208
Replies
2
Views
400

1,127,167
Messages
5,623,123
Members
415,956
Latest member
Footballtend

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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