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.
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º birds | 10000 | 10000 | 10000 | 10000 | 10000 | 10000 | ||
Quantity left to fill: | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | ||
Quantity for park | Quantity left to put | Pav 1 | Pav 2 | Pav 3 | Pav 4 | Pav 5 | Pav 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! | |