Formula for Goal Seek

[Odell Beckham]

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

 

[joeu2004]

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
	A	B	C	D	E	F
1		Curr Alloc	%Alloc	Reqd Alloc	New Alloc	%Alloc
2	Total	100		140	140
3	Investor 1	10	10.00%	20.00%	28	20.00%
4	Investor 2	20	20.00%	30.00%	42	30.00%
5	Investor 3	70	70.00%		70	50.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]

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]

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!