IF using the value calculated in an IF function and how to avoid a circular reference

[lepin]

Hi,

Ok I have an issue that I have put into a simplified table below.

The premise is that a business is purchasing units (of whatever) for a given value, however the supplier can only produce 1,000 units per month at max capacity.

The business will reinvest all the profit each month until it reaches the maximum output of the supplier.

At the end of month 6 the supplier output has almost maxed out and hence rather than reinvest the full profit, only the balance required to reach the 1,000 unit maximum would be carried forward as net cash and the remainder would be taken as a dividend.

The net cash figure is calculated as Profit minus Dividend.

Now, were I to use an IF formula that states if the cap is reached then value less cap is the figure to be entered into the dividend column that would be one thing, however the dividend column is a monetary value and not a unit value.

Also I want to be able to use the resultant figure calculated again in the spreadsheet herein shown as the JV dividend and dividend per partner (and this has caused issue in attempting even to copy and paste as value only).

All this can be achieved HOWEVER, I of course now need to have the net cash figure reduced at the end of month 6 to reflect the reduced investment requirement required to be taken forward into month 7 and this is where I get an understandable circular reference.

The net cash figure is affected by the dividend column, however this only calculates for month 7 once month 6 has been calculated and an exceedance reached and therefore reflects back into the month 6 calculations.

In the below I have manually input the required resultant figures but need a formula to resolve the matter as the unit price and unit output maximum can change and hence need the ability to automatically update.

I hope this makes sense

Many thanks for any assistance.

Unit price
Sales price	Max cap
		Max cap $

<tbody>
[TD="class: xl63, width: 79"] 100 [/TD]
[TD="width: 79"]Profit/unit[/TD]
[TD="width: 86, align: right"]150[/TD]
[TD="width: 79"][/TD]
[TD="width: 87"][/TD]
[TD="width: 82"][/TD]
[TD="width: 82"][/TD]

[TD="class: xl63"] 250[/TD]

[TD="class: xl63"] 1,000[/TD]

[TD="class: xl63"] 100,000[/TD]

[TD="class: xl70, width: 72"]Month[/TD]
[TD="class: xl64, width: 79"] Opening cash[/TD]
[TD="class: xl64, width: 79"] Units purchased & sold[/TD]
[TD="class: xl64, width: 86"] Profit[/TD]
[TD="class: xl64, width: 79"] Dividends[/TD]
[TD="class: xl64, width: 87"] Net cash[/TD]
[TD="class: xl64, width: 82"] JV Dividend[/TD]
[TD="class: xl64, width: 82"] JV Dividend per party[/TD]

[TD="class: xl71"]1[/TD]
[TD="class: xl74"] 1,000[/TD]
[TD="class: xl65"] 10[/TD]
[TD="class: xl65"] 1,500[/TD]
[TD="class: xl65"] -[/TD]
[TD="class: xl65"] 1,500[/TD]
[TD="class: xl78"][/TD]
[TD="class: xl79"][/TD]

[TD="class: xl72"]2[/TD]
[TD="class: xl75"] 2,500[/TD]
[TD="class: xl66"] 25[/TD]
[TD="class: xl66"] 3,750[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl66"] 3,750[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl82"][/TD]

[TD="class: xl72"]3[/TD]
[TD="class: xl75"] 6,250[/TD]
[TD="class: xl66"] 63[/TD]
[TD="class: xl66"] 9,375[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl66"] 9,375[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl82"][/TD]

[TD="class: xl72"]4[/TD]
[TD="class: xl75"] 15,625[/TD]
[TD="class: xl66"] 156[/TD]
[TD="class: xl66"] 23,438[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl66"] 23,438 [/TD]
[TD="class: xl77"][/TD]
[TD="class: xl82"][/TD]

[TD="class: xl72"]5[/TD]
[TD="class: xl75"] 39,063[/TD]
[TD="class: xl66"] 391[/TD]
[TD="class: xl66"] 58,594[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl66"] 58,594[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl82"][/TD]

[TD="class: xl72"]6[/TD]
[TD="class: xl75"] 97,656[/TD]
[TD="class: xl66"] 977[/TD]
[TD="class: xl66"] 146,484[/TD]
[TD="class: xl66"] 144,141[/TD]
[TD="class: xl66"] 2,343 [/TD]
[TD="class: xl67"] 144,141[/TD]
[TD="class: xl80"] 72,071[/TD]

[TD="class: xl72"]7[/TD]
[TD="class: xl75"] 100,000[/TD]
[TD="class: xl66"] 1,000[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl67"] 149,999[/TD]
[TD="class: xl80"] 75,000[/TD]

[TD="class: xl72"]8[/TD]
[TD="class: xl75"] 100,000[/TD]
[TD="class: xl66"] 1,000[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl67"] 149,999[/TD]
[TD="class: xl80"] 75,000[/TD]

[TD="class: xl72"]9[/TD]
[TD="class: xl75"] 100,000 [/TD]
[TD="class: xl66"] 1,000[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl67"] 149,999[/TD]
[TD="class: xl80"] 75,000[/TD]

[TD="class: xl72"]10[/TD]
[TD="class: xl75"] 100,000[/TD]
[TD="class: xl66"] 1,000[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl67"] 149,999[/TD]
[TD="class: xl80"] 75,000[/TD]

[TD="class: xl72"]11[/TD]
[TD="class: xl75"] 100,000[/TD]
[TD="class: xl66"] 1,000[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] 149,999[/TD]
[TD="class: xl66"] -[/TD]
[TD="class: xl67"] 149,999[/TD]
[TD="class: xl80"] 75,000[/TD]

[TD="class: xl73"]12[/TD]
[TD="class: xl76"] 100,000[/TD]
[TD="class: xl68"] 1,000[/TD]
[TD="class: xl68"] 149,999[/TD]
[TD="class: xl68"] 149,999[/TD]
[TD="class: xl68"] -[/TD]
[TD="class: xl69"] 149,999[/TD]
[TD="class: xl81"] 75,000[/TD]

</tbody>

 

[lepin]

Hi Scott T.
Very many thanks for resolving this final part of the problem.
Appreciate all your time and effort.
Best regards

 

[Scott T]

Glad to help