# limited recurring batch

#### cashmire

##### New Member
Hi,

I could use some help on this one. I'm working on the headcount forecast for the breeding operation. In short, they start with 300 animals which get impregnated. The offspring (50% male, 50% female) get returned into the breeding programme after 2yrs (female only). After 5yrs the original batch get send to the slaughter. I'm running into problems in year 5. How to run a formula that automates the elimination of the original batch while continue to add the new additions? Here is my preliminary layout...
Book1
BCDEFG
20Calving
21YearHeifersBullsPregnantHeifersBulls
22130025235112112
23229425230109109
24328824226107107
25439424309147147
26549623389185185
27659323465221221
287
298
309
3110
variables

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Harvey

##### Well-known Member
the problem is that you have never stored the "original" herd. You have to add somewhere what is the number of animals of the original herd.
Also, what is in C15 and C13?

#### fairwinds

##### MrExcel MVP
Hi,

As I see you posted this before and had no luck, I'll make a guess:

C26: =(C25+F23)-(C25*C\$15)-C22

C27: =(C26+F24)-(C26*C\$15)-F22 then dragged down with the rest of the formulas.

#### cashmire

##### New Member
Harvey,

in the cells are some constants which I was unable to post. Mainly % for mortality, etc.

-Peter

#### cashmire

##### New Member

Fairwinds,

thanks for your feedback. I tried it and unfortunately it didn't bring the results I was looking for. Problem is that I have problems with the constants which I am unable to paste for some funny reason.

#### cashmire

##### New Member

Fairwinds,

you were on the mark with your assumptions but the problem that I have is the following that I have to take into account a mortality rate of 2% each year, thus my initial batch of 300 reduces and after 5 years is not 300 anymore. I also have a mortality rate for the calfs. cell values:
C13 "calving ratio" 80%
C14 "Calf mortality" 5%
C15 "mortality" 2%

-Peter

#### Harvey

##### Well-known Member
make it:

=(C25+F23)-(C25*C\$15)-(C22*(1-((1-C\$15)^5)))

this subtracts the mortality of 2% for those 5 periods (or survival of 98%^5)

#### cashmire

##### New Member
Harvey,

thanks very much. I had no experience working with the "power to" formula but this seems to work fine. Appreciate your help!

-Peter

Replies
1
Views
10K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,263
Messages
5,769,111
Members
425,518
Latest member
seothaeng

### 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.

### Which adblocker are you using?

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

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