Circular dependency detected

Scarybuh

New Member
Joined
Nov 5, 2019
Messages
6
Hello,

I have to move a quantity of birds from origin houses to a destination house, the left most column is the origin, the destination houses are on top right side, pav 1, pav 2...
I tried the Min() function, so it would pick the minimum value from the "quantity for park" and the "maximum nºbirds" so it would put the 8000 birds in the pav1, as it's capacity is 10000. Then I wanted to keep the rest of available space (2000) in the cell above pav1, quantity left to fill, so I could pick again the min value between the next origin house and that value, the rest of space left in pav 1, so it would be filled. When filled, it would pass for the next pav 2.
Problem is, google spreedsheet is giving me the error: Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet settings.
Where I went to that setting and tried out some numbers, but I can't get it working, the other Min() functions bugs out and give me big values, bigger than amount of birds I have and the capacity of destination houses.

In the end, what I need is a formula that picks from the left side and puts in the middle cells until the maximum capacity is reached. While decreasing the value from the picked house.

Thanks.

Maximum nº birds100001000010000100001000010000
Quantity left to fill:#REF!#REF!#REF!#REF!#REF!#REF!
Quantity for parkQuantity left to putPav 1Pav 2Pav 3Pav 4Pav 5Pav 6
8000#REF!#REF!#REF!#REF!#REF!#REF!#REF!
12000#REF!#REF!#REF!#REF!#REF!#REF!#REF!
15000#REF!#REF!#REF!#REF!#REF!#REF!#REF!
20000#REF!#REF!#REF!#REF!#REF!#REF!#REF!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Giving a bit more info:
What I got in the #REF! of the left side is:
=K14-N14-O14-P14-Q14-R14-S14
The K14 is the value in Quantity for park, then I subtract the values on that line in the table. So I get the left over amount.

The #REF! on the upper part, Quatity left to fill, is
=N10-N14-N18-N22-N26
It's similar with the previous formula, but for columns now, it picks the max value of birds, then subtracts what already is filled in the houses, giving the left over amount.

The #REF! inside the table are:
=if(L14>0, Min($L14,N$11))
So, if there are left over birds, I want to pick the minmum value between how many birds left to pass and how much room left to put them in the destination house, thus leaving the Quantity left to fill at 0 or quantity left to put at 0 logically.
But as it is right now, excel says it's Circular dependency. Is there a way of doing what I want with diferent kind of formulas, not Circular dependency?
Thanks.
 
Upvote 0

Forum statistics

Threads
1,215,054
Messages
6,122,895
Members
449,097
Latest member
dbomb1414

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
Back
Top