Imperia Online International
October 28, 2021, 22:16:46 PM *
Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
 
   Home   Help Search Login Register  
Pages: [1] 2   Go Down
  Print  
Author Topic: Population growth above 100,000 - excel formula needed  (Read 7012 times)
0 Members and 1 Guest are viewing this topic.
AddisMakem
Pointillism is pointless
Lieutenant
***

Karma: +12/-5
Offline Offline

Posts: 191


Nil carborundum batardi


« 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?
Logged
Polymeron
Captain
****

Karma: +98/-103
Offline Offline

Posts: 304

Be the Storm


« Reply #1 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
Logged

Respect GW, or we'll see how good your AA is.
AddisMakem
Pointillism is pointless
Lieutenant
***

Karma: +12/-5
Offline Offline

Posts: 191


Nil carborundum batardi


« Reply #2 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
« Last Edit: April 13, 2009, 14:12:13 PM by AddisMakem » Logged
tiger
Community Manager
*

Karma: +694/-194
Offline Offline

Posts: 3319


« Reply #3 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.
Logged


tiger
Croatian Community Manager
Starbuck
Global Moderator
*

Karma: +396/-105
Offline Offline

Posts: 4472


Qui s'y frotte, s'y pique


« Reply #4 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 *wink*


have fun,
Logged

Deus populusque
AddisMakem
Pointillism is pointless
Lieutenant
***

Karma: +12/-5
Offline Offline

Posts: 191


Nil carborundum batardi


« Reply #5 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
Logged
chegewara
Guest
« Reply #6 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:
  • 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:
100k10
110k14
120k18
......
sumpar x

if you want i give you spread sheets *cool*
« Last Edit: April 13, 2009, 14:19:14 PM by chegewara » Logged
tiger
Community Manager
*

Karma: +694/-194
Offline Offline

Posts: 3319


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

« Last Edit: April 13, 2009, 14:28:50 PM by tiger » Logged


tiger
Croatian Community Manager
AddisMakem
Pointillism is pointless
Lieutenant
***

Karma: +12/-5
Offline Offline

Posts: 191


Nil carborundum batardi


« Reply #8 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.
Logged
AddisMakem
Pointillism is pointless
Lieutenant
***

Karma: +12/-5
Offline Offline

Posts: 191


Nil carborundum batardi


« Reply #9 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.
Logged
chegewara
Guest
« Reply #10 on: April 13, 2009, 14:32:54 PM »

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

Karma: +98/-103
Offline Offline

Posts: 304

Be the Storm


« Reply #11 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.
« Last Edit: April 13, 2009, 21:01:17 PM by Polymeron » Logged

Respect GW, or we'll see how good your AA is.
chegewara
Guest
« Reply #12 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
« Last Edit: April 13, 2009, 14:57:05 PM by chegewara » Logged
Polymeron
Captain
****

Karma: +98/-103
Offline Offline

Posts: 304

Be the Storm


« Reply #13 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.
« Last Edit: April 13, 2009, 15:17:10 PM by Polymeron » Logged

Respect GW, or we'll see how good your AA is.
chegewara
Guest
« Reply #14 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*
« Last Edit: April 13, 2009, 15:25:53 PM by chegewara » Logged
Pages: [1] 2   Go Up
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.12 | SMF © 2006-2009, Simple Machines LLC Valid XHTML 1.0! Valid CSS!