• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

distribution Cows

I have a large flock of dairy cows
A sample of cows was added to only 100 cows
I have a number of barns each of which has the capacity for a specific number of cows
I want to distribute cows in these barns
I divide the cows into groups according to milk yield for each cow so that each group has a difference of milk yield between cows within 5 liters.
I do this manually and this is very difficult for the whole flock
Is it possible to do this with Excel ?
I will be thankful to you



Excel Ninja
Mamdouh Elfors
I've seen cows too ...
but how do Your cows know, how much milk they could 'produce' with Your condition of capacity of barns?
Even 'my way' there would be more than 5ltr with H.C.1 ... hmm?
( as well with You Sample Results ... 9,2ltr ... )
Press [ Do It ].
If 5ltr is needed condition then capacity of barns should vary daily or how?


Thank you for the quick reply. This is what I promised you.
The proximity of cows in the average monthly milk production with each other is better. The difference is that the difference increases or decreases 5 liters between the cow and the other in the barn for the purpose of quickly detecting problems, nutrition, treatment, etc.
As you know, the data is a small sample. What if we apply the code to the 2000 head flock and thus increase the number of groups and the number and capacity of the barns. Will it work?
If necessary, send you the actual data

Peter Bartholomew

Well-Known Member
Why not sort the herd by yield and assign them to barns in sequence?
Assign the low yield cows to smaller barns because the variation in yield between individuals is greater.
The number of herds more than 2000 cows and the number of barns 26 to do it need a lot of time and effort and this is repeated at frequent intervals as the averages of production
It is not a requirement to be group in a barn. It is important that the barns be approximate in the production sequence


Excel Ninja
Mamdouh Elfors
You could add as many cows and barns as needed.
( hmm? terms ... In this version You have to name number of cows for each capacity of barns )
Take care that there are place for everyone.
... if no work ... then I could modify it as needs.

If ... that '5ltr grouping' needed then I can change it that way too ... then those capacities would vary as needed.
( but as written, You Sample result has 9,2ltr with '1st group' instead You named 5ltr ) ... hmm?


Well-Known Member
Why not sort the herd by yield and assign them to barns in sequence?
Assign the low yield cows to smaller barns because the variation in yield between individuals is greater.
This is what I've done in the attached using formulas.
In sheet distribution (2) I've allocated cows to barns simply in the order they appear in the Data sheet, which is sorted by Qu.Milk, largest to smallest.
Also, below each barn I've put the difference between the largest producing cow in that barn and the lowest producing cow in that barn.

Sheet distribution (3) is an attempt to lessen those differences by putting the barns in order of size and trying to follow, more or less, the distribution curve of the groups.
There is no maths behind this, I did it manually, assisted by the pivot table on that sheet at cell B30 which puts the data on the Data sheet into groups according to your 5 litre limits, and shows the number of cows in each group. Then I moved the barns around the sheet manually (just for a visual assessment), adjusted the formulae a bit and indeed the cows seem better grouped.

Now, to update where cows go, paste new data on the Data sheet, sort by Qu.Milk largest to smallest and the distribution sheets should update themselves.

With 2000 cows, and more barns would need more work. Depending on how often and how different the herd sizes change, it should be possible to automate not only the which cows are put into barns, but which barns are best used to give the better results.



Well-Known Member
In your example, you have only just enough space in the barns for your 100 cows, so the priority is to get them all in to those 7, variously-sized barns.
Taking the other extreme, you have as many barns available as you'll ever need, of whatever size you need, then you could easily choose barns to house your groups of 5 litres. The pivot shows you the numbers and capacities of barns you'd need:

Then to identify which cows go in each barn, expand the Qu.Milk column:

I expect your problem lies inbetween these two extremes, and we'd need an idea of what the priorities are: using the fewest barns and filling them up, or having the best grouping, or something else?