Target Land Calculator

[willymchilybily]

got it sorted read below.

 

[CFalcon]

I've been thinking that maybe there's a way to do it by splitting it up into sections of 550. The Multiplier increases by 1 every 550 acres, so for each section of 550 you can assume the multiplier is the same (and then do the same type of calculation as for under 999).

 

[willymchilybily]

I couldnt fit it all on one cell, because you can only get so many 'IF's. Copy the three code boxes into column a b and c repsectively. And wallah, land calculator. It only works up to a total of 3299 acres. If you want to buy more land than that your on your own.

the code in cell C9 in the printscreen was wrong but is correct in the code box

Column A

BUY ACRES
Current Acres
-
factor
multiplier
land bought > 999
land bought <999
land bought multiplier>1
land above multiplier>2
land above multiplier >3
land above multiplier>4
Total Cost

Column B

2100
5
-
14000
=ROUNDDOWN(IF((B2+B1)/550<1,1,(B2+B1)/550),0)
=IF(B1+B2<999,0,IF(B2<999,B2+B1-999,B1))
=B1-B6
=IF((B1+B2-1099)<0,0,IF(B2>1649,B9,IF((B1+B2-1099)<B1,B1+B2-1099,(B1))))
=IF((B1+B2-1649)<0,0,IF(B2>2199,B10,IF((B1+B2-1649)<B1,B1+B2-1649,B1)))
=IF((B1+B2-2199)<0,0,IF(B2>2749,0,IF((B1+B2-2199)<B1,B1+B2-2199,B1-B11)))
=IF((B1+B2-2749)<0,0,IF(B2>3299,0,IF((B1+B2-2749)<B1,B1+B2-2749,B1)))
-

Column C

-
-
-
-
-
=IF(B2>999,((B4+((8000)*(B2+1)))*B6)+((B6*(B6-1)/2)*8000),((B4+((8000)*(999+1)))*B6)+((B6*(B6-1)/2)*8000))
=((B4+(9000*(B2+1)))*B7)+((((B7^2)-B7)/2)*9000)
=IF(B2>1099,(((((8000)*(B2+1)))*B8)+((B8*(B8-1)/2)*8000)),(((((8000)*(1099+1)))*B8)+((B8*(B8-1)/2)*8000)))
=IF(B2>1649,(((((8000)*(B2+1)))*B9)+((B9*(B9-1)/2)*8000)),(((((8000)*(1649+1)))*B9)+((B9*(B9-1)/2)*8000)))
=IF(B2>2199,(((((8000)*(B2+1)))*B10)+((B10*(B10-1)/2)*8000)),(((((8000)*(2199+1)))*B10)+((B10*(B10-1)/2)*8000)))
=IF(B2>2749,(((((8000)*(B2+1)))*B11)+((B11*(B11-1)/2)*8000)),(((((8000)*(2749+1)))*B11)+((B11*(B11-1)/2)*8000)))
=C11+C10+C9+C8+C7+C6

 

[bluehen55]

So what exactly was the point of doing all of that in Excel? Just seems to make it more difficult.

 

[willymchilybily]

you have to pretty it up your self paste each collumn at the top of the page
click on cell c12
rightclick>format cells...>number tab>currency option>symbol =£ /decimal places = 0

 

[willymchilybily]

So what exactly was the point of doing all of that in Excel? Just seems to make it more difficult.

any one can use it. anywhere. at work during lunch break for example. and if you can't see any use for this then thats fine bluehen. me personally what i intend to do....

If one were inclined, one could figure out a tick by tick plan of what they should do at round start, what they make a tick. you know your land you know how much you can buy with your funds, and you know the cost of getting enough harvesters each tick for that land(hiring page). so on and so forth till one has a complete spreadsheet for round start however many ticks they want to run it for.

if one were even so ambitious they could graph potential money they earn a tick vs the cost of getting more land each tick and know the optimum point at which to stop buying land to develop thier routes first tech quickest. is it better to stop at 500 land and wait x ticks to get first dev. or spend a few more ticks buying land and take <x ticks to get the funds for thier development, including the offset of ticks lost in buying up to a higher land count

but If you dont see how thats useful. or cba to continue thats fine. but its what ill be looking at just for a little something to do before round start.

 

[bluehen55]

I just mean why in Excel, seems to be much more complicated to use Excel for it. Although I suppose it does make it more accessible for more people to use at any time like you said.

 

[CFalcon]

Excel is easier to tinker with values via trial and error. If you were to set this up in a program you'd have to re-run it for each trial.

Also, the intellectual rigor of it. I'm pretty sure I'll never use such a calculation, but I like mathematical challenges of this sort.

btw, so so close on the tail of getting this down to a single function. Just struggling with a power series that's appearing out of the end for some reason. You can do away with alot of the loops by taking the first and second derivatives of the function.

 

[timtadams]

Well after I got some sleep, i quickly put together a spreadsheet for getting costs for buying any amount of land, from any amount.

Im sure willy's works great, and i guess mine works the same way at the core, but its easy to calculate pretty much as much land as you want.

(n = land you want to buy
C = current land
buy to is calculated)

column 1:

n
C
buy to
-
-
M limits
1100
=A7+550
=A8+550

column 2:

5000
5
=B2+B1
-
to
999
=A7-1
=A8-1

column 3:

-
-
-
-
Multiplier
-
1
2

column 4:

-
-
-
-
loops
=IF(B3>B6, B6-B2, B3-B2)
=IF($B$3>B6, IF($B$3>B7, B7-B6, $B$3-B6), 0)
=IF($B$3>B7, IF($B$3>B8, B8-B7, $B$3-B7), 0)

column 5:

-
-
-
-
cumulative loops
=D6
=D7+E6
=D8+E7

column 6:

-
-
-
-
cost to
=D6*(14000 + 9000*(B2+(D6+1)/2))
=D7*14000+8000*C7*(D7*$B$2+(E7*(E7+1)-E6*(E6+1))/2)
=D8*14000+8000*C8*(D8*$B$2+(E8*(E8+1)-E7*(E7+1))/2)

column 7:

-
-
-
-
cumulatiuve total
=F6
=F7+G6
=F8+G7

Ive only shown formula to row 8 as on my image, but you can simply drag/expand down the bottom two rows as far as necessary to calculuate the land you want.

[edit]
you can empirically model the individual ranges accurately...
for ranges shown...
=4500*X^2+18500*X
=4000*X^2+18000*X+499500000
=8000*X^2+22000*X-4336100000
=12000*X^2+26000*X-15219500000
=16000*X^2+30000*X-34570700000

calc for X = current land + land to buy
and X = current land
and take difference

 

[CFalcon]

Solved the b*stard!

If you take the *third* derivative of the sum of total cost of all complete blocks of 550 acres, you get a triangular telescopic series, which can be implemented in a single excel cell without the use of macros/loops/iterations.

http://en.wikipedia.org/wiki/Tetrahedral_number

Forumla to follow shortly.

 

[CFalcon]

For target land of above 999, the total cost of that land (by total I mean as if you were buying from 5 acres up to that amount) is given by:

=(17453581000+(INT(C14/550)-3)*12103300000+((INT(C14/550)-3)*(INT(C14/550)-2)/2)*8467800000+(INT(C14/550)-3)*(INT(C14/550)-2)*(INT(C14/550)-1)*4840000000/6)+(MOD(C14;550)*(14000+8000*INT(C14/550)*((INT(C14/550)*550-1)+((MOD(C14;550)+1)/2))))

where C14 is ((land to be bought) + 6)

This works precisely for all values above 999, works in a single cell, and doesn't need any nasty macros

So to work out the cost of a transaction when starting from a current land =/= 5 (and above 999), you simply do the above for C14=landtobuy+currentland+6 and then take away the above for C14=currentland+1. If currentland is between 5 and 999 you'll need to use one of the other formulas people have come up with which are relevant to that range.

/genius

Edit: INT() and MOD() are functions in OpenOfficeCalc, there will be equivalents in Excel but I don't know for certain whether the syntax is the same.

2nd Edit: Apparently this formula works in the 1000-1099 range as well, which I didn't think it would. But not complaining \o/

 

[timtadams]

For target land of above 1099, the total cost of that land (by total I mean as if you were buying from 5 acres up to that amount) is given by:

=(17453581000+(INT(C14/550)-3)*12103300000+((INT(C14/550)-3)*(INT(C14/550)-2)/2)*8467800000+(INT(C14/550)-3)*(INT(C14/550)-2)*(INT(C14/550)-1)*4840000000/6)+(MOD(C14;550)*(14000+8000*INT(C14/550)*((INT(C14/550)*550-1)+((MOD(C14;550)+1)/2))))

where C14 is ((land to be bought) + 6)

This works precisely for all values above 1099, works in a single cell, and doesn't need any nasty macros

So to work out the cost of a transaction when starting from a current land =/= 5, you simply do the above for C14=landtobuy+6 and then take away the above for C14=currentland+1

/genius

Edit: INT() and MOD() are functions in OpenOfficeCalc, there will be equivalents in Excel but I don't know for certain whether the syntax is the same.

I don't know how you did that, but i am very impressed.

and the syntax is almost the same, only difference is that function options are separated by a comma, and not a colon.
ie, MOD(number;divisor) becomes MOD(number,divisor)
(im just assuming that this difference is common thoughout all functions)

 

[CFalcon]

I don't know how you did that, but i am very impressed.

Work out a list of the individual costs of 550 acre blocks, make a list of the differences between those costs, make a list of the differences of the differences, then a list of the differences of the differences of the differences. Eventually you hit a geometric progression which can be expressed as a sum from 1 to n (which is something a single cell can handle, with a bit of fiddling).

Or if you prefer; witchcraft.

 

[tobapopalos]

A wizard did it.

 

[willymchilybily]

For target land of above 999, the total cost of that land (by total I mean as if you were buying from 5 acres up to that amount) is given by:

=(17453581000+(INT(C14/550)-3)*12103300000+((INT(C14/550)-3)*(INT(C14/550)-2)/2)*8467800000+(INT(C14/550)-3)*(INT(C14/550)-2)*(INT(C14/550)-1)*4840000000/6)+(MOD(C14;550)*(14000+8000*INT(C14/550)*((INT(C14/550)*550-1)+((MOD(C14;550)+1)/2))))

where C14 is ((land to be bought) + 6)

This works precisely for all values above 999, works in a single cell, and doesn't need any nasty macros

So to work out the cost of a transaction when starting from a current land =/= 5 (and above 999), you simply do the above for C14=landtobuy+currentland+6 and then take away the above for C14=currentland+1. If currentland is between 5 and 999 you'll need to use one of the other formulas people have come up with which are relevant to that range.

very nice, but i cant get it to work on excel. i switch colon to comma and kept mod as mod. What does the mod function do in open office? because at the moment buying 1206 land (from 5) gives £7,306,405,000 not £7,422,505,000
(infact none of the value work in excell you meanie. ) does it not have an upper limit on the land buying? aka the mod keeps updating? i didnt follow where you got the long numbers in some of the brackets or why.

Well after I got some sleep, i quickly put together a spreadsheet for getting costs for buying any amount of land, from any amount.

Im sure willy's works great, and i guess mine works the same way at the core, but its easy to calculate pretty much as much land as you want.

hate to be the barer of bad news but your calculator does not work. I dunno why
my favourite test is if you have 1100 acres and buy to 2100 acres. thats the general nut buster i find..

your calculator gives the wrong value. it should return £134,731,000,000 *pats his calculator*

The problem i had that you may be having is... if your starting land is above say one of the boundaries.. 999/1099/1649/2199 etc etc you need to change the formula hence my if statements in column C. im not sure this is your problem though as you only have a very small error

 

[CFalcon]

For target land of above 999, the total cost of that land (by total I mean as if you were buying from 5 acres up to that amount) is given by:

=(17453581000+(INT(C14/550)-3)*12103300000+((INT(C14/550)-3)*(INT(C14/550)-2)/2)*8467800000+(INT(C14/550)-3)*(INT(C14/550)-2)*(INT(C14/550)-1)*4840000000/6)+(MOD(C14;550)*(14000+8000*INT(C14/550)*((INT(C14/550)*550-1)+((MOD(C14;550)+1)/2))))

where C14 is ((land to be bought) + 6)

This works precisely for all values above 999, works in a single cell, and doesn't need any nasty macros

So to work out the cost of a transaction when starting from a current land =/= 5 (and above 999), you simply do the above for C14=landtobuy+currentland+6 and then take away the above for C14=currentland+1. If currentland is between 5 and 999 you'll need to use one of the other formulas people have come up with which are relevant to that range.

very nice, but i cant get it to work on excel. i switch colon to comma and kept mod as mod. What does the mod function do in open office? because at the moment buying 1206 land (from 5) gives £7,306,405,000 not £7,422,505,000
(infact none of the value work in excell you meanie. ) does it not have an upper limit on the land buying? aka the mod keeps updating?

You're putting C14 as land to be bought. C14 is landtobebought+6

If you put 1200 into the land management page, you get 7,306,405,000, which is the value you're producing for 1206.

So just +6 to the input

 

[timtadams]

hate to be the barer of bad news but your calculator does not work. I dunno why
my favourite test is if you have 1100 acres and buy to 2100 acres. thats the general nut buster i find..

your calculator gives the wrong value. it should return £134,731,000,000 *pats his calculator*

The problem i had that you may be having is... if your starting land is above say one of the boundaries.. 999/1099/1649/2199 etc etc you need to change the formula hence my if statements in column C. im not sure this is your problem though as you only have a very small error

indeed. I didn't test it rigorously, just compared a few numbers to results from the land managment page.
Ive just had a 2 min look at my workbook, and i think i might know what the problem is. I'll update if i work it out.