Title: Population growth above 100,000 - excel formula needed Post by: AddisMakem on April 13, 2009, 10:45:51 AM 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? Title: Re: Population growth above 100,000 - excel formula needed Post by: Polymeron on April 13, 2009, 12:43:56 PM 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 (http://www4.imperiaonline.org/forums/int/index.php?topic=768.0) Title: Re: Population growth above 100,000 - excel formula needed Post by: AddisMakem on April 13, 2009, 14:03:43 PM 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 Title: Re: Population growth above 100,000 - excel formula needed Post by: tiger on April 13, 2009, 14:11:06 PM 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.
Title: Re: Population growth above 100,000 - excel formula needed Post by: Starbuck on April 13, 2009, 14:12:52 PM 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, Title: Re: Population growth above 100,000 - excel formula needed Post by: AddisMakem on April 13, 2009, 14:15:06 PM 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 *freak* Appreciate your help and time Title: Re: Population growth above 100,000 - excel formula needed Post by: chegewara on April 13, 2009, 14:15:45 PM everything is in that topic, but you want ready formulas to excel sheet.
i was make it on few steps:
parameter is counted on table:
if you want i give you spread sheets *cool* Title: Re: Population growth above 100,000 - excel formula needed Post by: tiger on April 13, 2009, 14:21:49 PM 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. Title: Re: Population growth above 100,000 - excel formula needed Post by: AddisMakem on April 13, 2009, 14:23:52 PM Thanks all
Chegerawa: knew you would have worked it out! *bravo* Any chance you could post or pm the actual formula? My brain is of limited use these days :head_hurts_kr: and while I could probably puzzle it out your knowledge is so much better and your formulae are much more elegant and succinct. Title: Re: Population growth above 100,000 - excel formula needed Post by: AddisMakem on April 13, 2009, 14:27:14 PM 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. Title: Re: Population growth above 100,000 - excel formula needed Post by: chegewara on April 13, 2009, 14:32:54 PM http://www.speedyshare.com/data/632612123/16242769/78508439/growth%26limit.xls
Title: Re: Population growth above 100,000 - excel formula needed Post by: Polymeron on April 13, 2009, 14:42:32 PM 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. Title: Re: Population growth above 100,000 - excel formula needed Post by: chegewara on April 13, 2009, 14:56:03 PM 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 *cool* no matter how, but matter is work ours formulas too Title: Re: Population growth above 100,000 - excel formula needed Post by: Polymeron on April 13, 2009, 15:15:09 PM 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. Title: Re: Population growth above 100,000 - excel formula needed Post by: chegewara on April 13, 2009, 15:22:53 PM 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 *cool* Title: Re: Population growth above 100,000 - excel formula needed Post by: Polymeron on April 13, 2009, 21:01:46 PM See correction to Cell 2 in my original post.
EDIT: Some checking showed that the formulas did not give a correct result for crowded (>100,000 pop) provinces. I've made some corrections that give a correct result. These are: - Not incremental, but rather smooth, crowding bonus. - Guild bonus applied to Cell 4 and omitted from Cell 1, as suggested by chegwara - thanks, I might not have caught that one without your help. The new formulas give me a correct result for one example, see if they always work. Correct formulas (hopefully): Cell 1: =(30+Current_population/1000+Granary_level)*(1+(0.05*Medicine_level))*(1+terrain bonus) Cell 2: =(Current_population-90000)/10000 Cell 3: =(8+C2*2)*C2 Cell 4: =IF(Current_population<100000,C1,C1-C3)*(1+(0.01*Healers_guild_level)) ...Where Cell #4 should give you a correct result every time now. This also means that the other approach will usually give a result that is off by a little bit. Title: Re: Population growth above 100,000 - excel formula needed Post by: Polymeron on April 13, 2009, 21:18:57 PM Nope, should be as I put it in the last post. Looks like it's not incremental after all.
That was only an assumption, you know :) Please tell me how the formula reacts with actual data! Title: Re: Population growth above 100,000 - excel formula needed Post by: SEN on April 13, 2009, 22:32:10 PM The new formula works for all my provinces *ok* Now i need to get it into my excel *gamer* |