Formula required for milk production regime

Hello, Aqeel!

I don't use Numbers but I am bumping this message as it is unique but needs a practical solution. It is doable. You would need some calculation that adjusts for the aging, as you write, assuming that cows can give 10 liters for each of 2 months and then 5 liters per month from then on.

There are experienced Numbers users here who would surely be able to give you an elegant solution. They include Wayne Contello, Jerrold Green1, Barry, Yellowbox, and Badunit *. So perhaps one of them will show up.

* My apologies to those whom I left out.

Hi Aqeel,

An interesting question! I thought it would be easier to consider each cow separately, then we can add litres/day for the whole herd.

I am not a farmer. Does this seem reasonable?

Regards,

Ian.

for the first two months cows will produce 10 litres/cow then they will decrease it to 8 litre/cow in the third month. In the fourth month the milk production becomes 5 litre/cow and in the fifth month it becomes 2 litres/cow. Every new addition in the herd will follow the same pattern.

Are you trying to do something like this?

In C3, copied right and down: =IFERROR($A3×VLOOKUP(C$2−$B3+1,PerCow::$A:$B,2,0),"")

This assumes the cows are "put out to pasture" after the 5th month following the month they were added to the herd, and thus stop producing. But if that's not generally the case you can just extend the two tables.

SG

Hello

You may use SUMPRODUCT() to calculate the total litres for each month such that -

∑ { [number of cows of i'th month] * [liters per cow of i'th month] } : i = 1..N

E.g., (N = 6)

Litres A1 month A2 6 A3 5 A4 4 A5 3 A6 2 A7 1 B1 litre/cow B2 1 B3 2 B4 5 B5 8 B6 10 B7 10

Table 1 A1 month A2 * A3 * A4 * A5 * A6 * A7 Jan A8 Feb A9 Mar A10 Apr A11 May A12 Jun B1 herd size B2 0 B3 0 B4 0 B5 0 B6 0 B7 =B6+C7 B8 =B7+C8 B9 =B8+C9 B10 =B9+C10 B11 =B10+C11 B12 =B11+C12 C1 new cows C2 0 C3 0 C4 0 C5 0 C6 0 C7 10 C8 10 C9 0 C10 0 C11 0 C12 20 D1 total litres D2 D3 D4 D5 D6 D7 =SUMPRODUCT(C2:C7,Litres::B$2:B$7) D8 =SUMPRODUCT(C3:C8,Litres::B$2:B$7) D9 =SUMPRODUCT(C4:C9,Litres::B$2:B$7) D10 =SUMPRODUCT(C5:C10,Litres::B$2:B$7) D11 =SUMPRODUCT(C6:C11,Litres::B$2:B$7) D12 =SUMPRODUCT(C7:C12,Litres::B$2:B$7) E1 litres/cow E2 E3 E4 E5 E6 E7 =D7/B7 E8 =D8/B8 E9 =D9/B9 E10 =D10/B10 E11 =D11/B11 E12 =D12/B12

Rows 2 thru 6 in Table 1 are required to keep SUMPRODUCT() from throwing error in rows 7 thru 11.

* Tables are built in Numbers v2.

Hope this may help you get the basic idea.

H

Hello

Probably the following formulae are better suited for column B in Table 1.

Table 1 B1 herd size B2 0 B3 0 B4 0 B5 0 B6 0 B7 =SUM(C2:C7) B8 =SUM(C3:C8) B9 =SUM(C4:C9) B10 =SUM(C5:C10) B11 =SUM(C6:C11) B12 =SUM(C7:C12)

This way the herd only consists of cows added in the last N = 6 months.

Regards,

H

Just being curious; what will be the formula if we keep the number of animals constant for few months?

How about something like this? You add a row that calculates the Herd Size in the Footer Rows at the bottom of the table. Then you add body rows to the Estimated Production table and enter the Cows Added and Month Added that result in a Herd Size that stays constant at whatever level you want. It takes a little trial and error but shouldn't be too hard. Here I quickly saw that I needed to add 10 cows in month 6 and 15 in month 7 in order to keep the herd size at 40. In month 8, 10 more need to be added, so add a body row to the table and enter 10 and 8 in columns A and B.

The formula in C12, copied right: =SUMIFS($A,$B,"<="&C2,$B,">"&C2−5).

SUMIFS(<the column to sum>,<first test column>,<condition to apply>,<second test column>,<condition to apply>)

This tells Numbers to sum the numbers in column A where the number in column B is less than or equal to C2 (don't count cows added in the future) and where the number in column B is greater than or equal to C2-5 (don't count cows added so long ago they aren't in the herd any more).

This, as before, assumes a cows produces for 5 months only, and then leaves the herd. You may have to adjust those assumptions to fit the actual situation.

SG

Is there any way I can share the whole numbers sheet?

You mean share the whole Numbers document? (I don't think there's an easy way to share just one sheet). To share a document you can use the Share menu and post the iCloud link. Or you can put the document in Dropbox or similar and post the link to it there.

If you decide to do that, be sure to remove any confidential information before sharing the link.

SG

I have had a look. I don't follow everything you are doing, but have a look at the text box I put on in Milk Production sheet with a function that you might find useful:

=MAX(A-B,0) will effectively "zero out" any negative values resulting when you subtract value B from value A as I understand you are trying to do. That way, you don't have to resort to a custom data format.

SG