Formula for Goal Seek

Odell Beckham

New Member
Joined
Dec 17, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Mar 2, 2014
Messages
3,008
Office Version
  1. 2010
Platform
  1. Windows
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. :sneaky:

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
Joined
Mar 2, 2014
Messages
3,008
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Mar 2, 2014
Messages
3,008
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top