Population growth above 100,000 - excel formula needed

[AddisMakem]

As I understand it the excel formula for population growth below 100,000 is:

=(30+(Current_population/1000)+Granary_level)*(1+(0.05*Medicine_level))*(1+province terrain bonus)*(1+(0.01*Healer_s_guild_level))

I know that population growth slows and eventually stops after 100,000 but I can't get my head around how to adapt the formula so it applies in all situations.  From reading previous posts the excellent Chegerawa had posted a link that might help but the link no longer works.  Can anyone help?

[Polymeron]

Check this one out, it explains the growth loss past 100k (among other things):

http://www4.imperiaonline.org/forums/int/index.php?topic=768.0

[AddisMakem]

Thanks Polymeron

Appreciate the reference and your time.  From re-reading the post as far as I can see the problem for the excel formula is if the formula should adjust incrementally or linearly.

If it's the first then the formula is relatively easy to adjust, I think. You adjust the formula for less than 100,000 so that above 100,000 it reads instead (the formula is for when population is between 100,000 and 110,000):

=If(Current_population<100000,(30+(Current_population/1000)+Granary_level)*(1+(0.05*Medicine_level))*(1+terrain bonus)*(1+(0.01*Healer_s_guild_level)),(30+(100000/1000)+Granary_level)*(1+(0.05*Medicine_level))*(1+terrain bonus)*(1+(0.01*Healer_s_guild_level))-10)

Probably some redundant parentheses here!

Formula will need to be adjusted once population is >110,000 but it seems to work when I use my actual population, research levels etc up to 110,000.

On the other hand if you only lose the full 10 when you reach 110,000 and less if you are at say 105,000 the loss in growth is relatively less then the formula is more complicated.

Would love any more support, guidance that can be offered

[tiger]

As i remember, breaking points are at 100k, 110 k and so on. for 109999 population you lose 10. for 110000 you lose 10 + 14=24. and you lose 24 till 119999. then you lose 10+14+18 = 42 and so on. no complicated formula between.

[Starbuck]

as explained in the manual, the formula should be incremental ... but empirically, there has to be a linear component to explain the variation at the margins (max +/- 2 villagers/hrs).
this is best seen when the growth rate is still strong :
 - it could be because of a time-delaying problem in the updating of the growth rate in relation to the actual population of the province.
 - or it could be that there is a missing marginal linear component

... overall, the formula is 98% correct


have fun,

[AddisMakem]

Thanks Tiger

I'll use the incremental formula and check it against empire reports to see if it stays consistent.

Going to be a nightmare nested if statement 

Appreciate your help and time

[chegewara]

everything is in that topic, but you want ready formulas to excel sheet.
i was make it on few steps:

• first check is less than 100k, if true simple formula if not second step
• check in which the is, and calculate special parameter
• count sum for all numbers from table

parameter is counted on table:

100k	10
110k	14
120k	18
...	...
sum	par x

if you want i give you spread sheets

[tiger]

10   10
14   24
18   42
22   64
26   90
30   120
34   154
38   192
42   234
46   280
50   330
54   384
58   442
62   504
66   570
70   640
74   714
78   792
82   874
thats factors and sum till that value in right column. just copy paste it and use in excel.
if you start in cell a1 - lA2  =a1 + 4 and you drag that line as far as you wish.
right column B2 = SUM($A$1:A2) and you again just drag that formula as long as you wish.



   Pop in Plains   Pop in PWF
99000   326,16   298,98
100000   318,32   290,96
110000   325,92   296,76
120000   329,52   298,56
130000   329,12   296,36
140000   324,72   290,16
150000   316,32   279,96
160000   303,92   265,76
170000   287,52   247,56
180000   267,12   225,36
190000   242,72   199,16
200000   214,32   168,96
210000   181,92   134,76
220000   145,52   96,56
230000   105,12   54,36
240000   60,72   8,16
250000   12,32   -42,04
260000   -40,08   -96,24
270000   -96,48   -154,44
280000   -156,88   -216,64
 even better. compare table for Plains and plains in forest i used in nomads invasion. data used are med 16 and granaries 22.

[AddisMakem]

Thanks all

Chegerawa:  knew you would have worked it out! 

Any chance you could post or pm the actual formula?  My brain is of limited use these days    and while I could probably puzzle it out your knowledge is so much better and your formulae are much more elegant and succinct.

[AddisMakem]

Sorry again!

My last post crossed with Tiger's and I missed Chegerawa's last comment on his post

Would love the spreadsheet!  Thanks you.  Karma to all of you.

[chegewara]

http://www.speedyshare.com/data/632612123/16242769/78508439/growth%26limit.xls

[Polymeron]

Come on, guys, this is silly. Assuming it's incremental (didn't check), it can be done in a single excel formula. Although, for simplicity, I'd use four, presented below. Variables in Red.

Cell 1: =(30+Current_population/1000+Granary_level)*(1+(0.05*Medicine_level))*(1+terrain bonus)*(1+(0.01*Healers_guild_level))
Cell 2: =INT((Current_population-90000)/10000)
Cell 3: =(8+C2*2)*C2
Cell 4: =IF(Current_population<100000,C1,C1-C3)

...Where Cell #4 should give you a correct result every time. You may want to change it to Bold.


I'm surprised I didn't include that in the economy calculator I made back in the day. I really need to update that thing, but far too busy with other stuff...



If you didn't get the formula in Cell 3, read a little about Gauss. The legend is he came up with this at age 6. Hint: I started with =10*C2+(C2-1)*C2*4/2


EDIT: Cell 2 contained an error which I was sure I fixed but, apparently was still there.

[chegewara]

Come on, guys, this is silly. Assuming it's incremental (didn't check), it can be done in a single excel formula. Although, for simplicity, I'd use four, presented below. Variables in Red.

Cell 1: =(30+Current_population/1000+Granary_level)*(1+(0.05*Medicine_level))*(1+terrain bonus)*(1+(0.01*Healers_guild_level))
Cell 2: =INT((Current_population-100000)/10000)
Cell 3: =(8+C2*2)*C2
Cell 4: =IF(Current_population<100000,C1,C1-C3)

...Where Cell #4 should give you a correct result every time. You may want to change it to Bold.


I'm surprised I didn't include that in the economy calculator I made back in the day. I really need to update that thing, but far too busy with other stuff...



If you didn't get the formula in Cell 3, read a little about Gauss. The legend is he came up with this at age 6. Hint: I started with =10*C2+(C2-1)*C2*4/2

i think is one mistake, healers guild is counted on formula on end and should be counted in cell 4 on yours calcs

no matter how, but matter is work ours formulas too

[Polymeron]

I have to admit, I've never played a realm with guilds, only GW (where they came from originally). I don't see why it should work differently from medicine. But in any case I copied it from Addis Makem's formula...

=If(Current_population<100000,(30+(Current_population/1000)+Granary_level)*(1+(0.05*Medicine_level))*(1+terrain bonus)*(1+(0.01*Healer_s_guild_level)),(30+(100000/1000)+Granary_level)*(1+(0.05*Medicine_level))*(1+terrain bonus)*(1+(0.01*Healer_s_guild_level))-10)

"copy and paste it into your formula" isn't, in itself, a formula. Also, the need for a whole spreadsheet when you can use four cells is cumbersome. This is neater but, more importantly, I believe this is what was asked in the first place - a formula that gives a correct result.

[chegewara]

Cell 1: =(30+Current_population/1000+Granary_level)*(1+(0.05*Medicine_level))*(1+terrain bonus)
Cell 2: =INT((Current_population-100000)/10000)
Cell 3: =(8+C2*2)*C2
Cell 4: =IF(Current_population<100000,C1,C1-C3)*(1+(0.01*Healers_guild_level))

thats should be with guilds, im not shure about cell 3, but i believe you with