Battlegrounds Spreadsheet

73 Replies
devnullicus
22 January, 2016, 11:52 PM UTC

Please note that there is a new version (4.0) of the spreadsheet now at

 https://docs.google.com/spreadsheets/d/1WebO4sDFZLlXbz7x6332oheMSNXK2H2LQLw4uJwJf0M/edit#gid=1434820704


Changes for version 4.0 include:

  • Fixed a number of data errors   
  • Added Reavers to Offensive BG unit list   
  • Recalculated unit efficiency for BGs to be resources/min   
  • Changed scripts to default to barbarian instead of archer for defensive BGs Inf/Cav   
  • Refactored "Remaining Balance" so that it shows what level BG will currently cause a payout   
  • Added resource Win/Loss ratios to bank   
  • Added tracking of attack and defense ratings win/loss ratios   
  • Updated instructions   
  • Added valuation for sapphires for calculation purposes
  •    (note you can change the value - I have it set to 750 gold/iron/or food per sapphire. Based on being able to buy 30k for 40 sapphires)  

If you ever want to know how the gods feel about money, simply look at who they choose to give it to [Caine Black Knife]
UTC -8:00
2
jumy
30 January, 2016, 11:18 AM UTC

thanks for your work, please consider also huntress

The truth hurts
UTC +12:00
1
devnullicus
31 January, 2016, 5:21 AM UTC
Did I forget huntress somewhere?   Can you elaborate on what you mean?
If you ever want to know how the gods feel about money, simply look at who they choose to give it to [Caine Black Knife]
UTC -8:00
1
jumy
31 January, 2016, 9:52 PM UTC

in Units to Send  and Own Units Killed 


The truth hurts
UTC +12:00
1
devnullicus
1 February, 2016, 3:44 AM UTC
AH, you're right.  Will fix that for the next version.  Thanks for letting me know.
If you ever want to know how the gods feel about money, simply look at who they choose to give it to [Caine Black Knife]
UTC -8:00
1
devnullicus
5 February, 2016, 11:14 PM UTC

OK, updated Spreadsheet to Version 4.2.   Changes are:

  • Modified BG Payout to be clearer. It just shows Bank Credits and what level of BG will currently give a payout. Much simpler to understand.
  • Added instructions for an interesting new experimental BG method
  • Added Huntress to Def BG units

Same URL as the 4.0/4.1 spreadsheet, i just updated it in place.

If you ever want to know how the gods feel about money, simply look at who they choose to give it to [Caine Black Knife]
UTC -8:00
1
jumy
6 February, 2016, 5:56 PM UTC

thanks very much, how about adding tournament points?

how do transfer my bank from 4 to 4.2 ?

The truth hurts
UTC +12:00
1
devnullicus
6 February, 2016, 7:31 PM UTC

I actually did make a stab at adding in rewards from BG tournaments to the ratio calculator, just forgot to mention it in the changes list, I think.  Take a look at the bottom of the bank tab.   Unfortunately, I don't think it's complete yet as I didn't add space for the daily rewards which has a lot more types of things you can get.   It's a work in progress :)


As for copying from 4.0->4.2, that is something I hadn't really thought about until last night.   It's tricky because you don't want to just cut-n-paste areas because the formulas might have changed. 


For the bank page, you can safely cut and paste the following columns "Last Large Payout", "Overflow Killed (Credits)", "Overflow Number Rewarded (Debits)" from "Archer" through "Food".   Be careful though because Reavers and Huntress were added, so you'll have to make sure the old values are in the proper rows on the new spreadsheet.


In order for the bank to be correct, you'll ALSO need to copy the "Previously Accumulated Totals" columns on the Defensive and Offensive BG tabs.  You'll need to grab the rows for "Own Units Killed" and "Rewards" ("Initial Enemy Units" and "Enemy Killed Rows" are optional - they're just fun data to know).  Again, be careful to make sure the values get copied to the correct new rows because of the addition of Reaver and Huntress.


Edit: to more easily determine which places you need to copy, I've modified the 4.2 version to have the cells that you need to copy have a light orange background.

For all other pages, you should just be able to safely copy and paste any cells that have a white background.

I'm not sure if there's a way to automate this - there might be.  I'll give it some thought and see if I can't figure out a way.  For now, those instructions will have to do.





If you ever want to know how the gods feel about money, simply look at who they choose to give it to [Caine Black Knife]
UTC -8:00
1
1donutboy
6 February, 2016, 8:55 PM UTC
This is extremely helpful and appreciated.  If I may suggest one thing, that would be to automatically populate the troops needed.  It takes me about 5 minutes to manually go to each warrior type and determine the numbers.  Several times I did not have anywhere near the troop type required.  If autoppopulated, I could just glance at the entire list and focus on the missions I could handle.  Still this is a great tool the way it is.  Thank you very much for putting this together...no easy feat.
UTC +8:00
1
jumy
6 February, 2016, 11:24 PM UTC
devnullicus said:

I actually did make a stab at adding in rewards from BG tournaments to the ratio calculator, just forgot to mention it in the changes list, I think.  Take a look at the bottom of the bank tab.   Unfortunately, I don't think it's complete yet as I didn't add space for the daily rewards which has a lot more types of things you can get.   It's a work in progress :)


As for copying from 4.0->4.2, that is something I hadn't really thought about until last night.   It's tricky because you don't want to just cut-n-paste areas because the formulas might have changed. 


For the bank page, you can safely cut and paste the following columns "Last Large Payout", "Overflow Killed (Credits)", "Overflow Number Rewarded (Debits)" from "Archer" through "Food".   Be careful though because Reavers and Huntress were added, so you'll have to make sure the old values are in the proper rows on the new spreadsheet.


In order for the bank to be correct, you'll ALSO need to copy the "Previously Accumulated Totals" columns on the Defensive and Offensive BG tabs.  You'll need to grab the rows for "Own Units Killed" and "Rewards" ("Initial Enemy Units" and "Enemy Killed Rows" are optional - they're just fun data to know).  Again, be careful to make sure the values get copied to the correct new rows because of the addition of Reaver and Huntress.


Edit: to more easily determine which places you need to copy, I've modified the 4.2 version to have the cells that you need to copy have a light orange background.

For all other pages, you should just be able to safely copy and paste any cells that have a white background.

I'm not sure if there's a way to automate this - there might be.  I'll give it some thought and see if I can't figure out a way.  For now, those instructions will have to do.





for cut and paste you can use "paste special- only value" and dont touch any formula
The truth hurts
UTC +12:00
1
devnullicus
7 February, 2016, 1:11 AM UTC

1DonutBoy:  I do, actually.  On the defensive BGs, you just choose the troop types and it tells you the amount needed and in the Offensive BGs, it tells you how many of the "optimal" troop types you need in the "Number and Cost to Yellow" and "Number and Cost to Kill" rows (35-56)

Jumy: hahaha, yeah, that would work too.  Much easier lol.



If you ever want to know how the gods feel about money, simply look at who they choose to give it to [Caine Black Knife]
UTC -8:00
1
lbean4
12 February, 2016, 1:52 AM UTC

Thank you.  Yoour time and effort are greatly appreciated.


Respectfully,

Lady Elocineaux
UTC +8:00
-1
jumy
17 February, 2016, 5:36 PM UTC

proposal for a addition bank resources split gold-iron-food or in % (for study :D)

The truth hurts
UTC +12:00
1
devnullicus
18 February, 2016, 12:14 AM UTC

Jumy: Hmmm, wouldn't be too hard to add, actually.  I will consider this for next version.


If you ever want to know how the gods feel about money, simply look at who they choose to give it to [Caine Black Knife]
UTC -8:00
1
jumy
18 February, 2016, 4:58 PM UTC

wauu and and always thanks :)

The truth hurts
UTC +12:00
1
Keaton
16 April, 2016, 9:44 PM UTC
Just tried it out on a level 38 - this thing isn't even yellow lined yet, and the spreadsheet says I have killed 114% of the battleground... bad sign... 
UTC +0:00
1
jumy
16 April, 2016, 9:55 PM UTC

bgs has changed a lot.... not according plarium

The truth hurts
UTC +12:00
1
AlberBeni
17 April, 2016, 5:13 PM UTC
Hello, first time congratulations for your spreadsehhet, i have some days interested in know how works the BG´s, i found some spreadsheets on google and your is the most complete i been watch, but my native language is spanish, its some dificult for me follow instructions an fill it correctly, im lost filling it... my question.. arent any tutorial about how to refill the spreadsheet? will be so much pareciated. Regards
UTC +2:00
1
Haku
19 April, 2016, 11:55 AM UTC
thanks.
UTC +9:00
1
WRATH
26 April, 2016, 2:34 PM UTC
Hmm...interesting !
fabZ
UTC +2:00
0
1781159 users registered; 48443 topics; 287481 post; our newest member:buyukkralali