Required Experience and Food calculator for any hero

reference
items

#21

Some of us want to know the cost! :wink:


#22

Thanks for the XP formulas, @thrashina . I was working on my own leveling costs SS, and your work saved me a good bit of guessing/stumbling in the dark trying to do curve fitting!

If the macros survive intact when you import this to google docs, that version would be easily shared.

Even without the macros, those of us who are better with spreadsheets could probably put it to good use…


#23

agree with @BarryWuzHere. If you could just upload it to Google Sheets @thrashina, one of us can get it working there and shareable.


#24

Here is the xlsm sheet uploaded to google sheets.

There was a macro button for the Hero calc from level A to Level B - but the macro did not translate - so here is the VBA code I wrote for the button:

Sub calcexp()
'From input data in BLUE
StartLev = Cells(1, 8)
EndLev = Cells(2, 8)
ASCENStart = Cells(3, 8)
ASCENEnd = Cells(4, 8)
Stars = Cells(5, 8)
Foodoffset = 5
If Stars = 1 Then
cr = 7 'Column of EXP data
as1 = 10 'Max Lev at Asc
as2 = 20
as3 = 0
as4 = 0
ElseIf Stars = 2 Then
cr = 26
as1 = 20
as2 = 30
as3 = 40
as4 = 0
ElseIf Stars = 3 Then
cr = 46
as1 = 30
as2 = 40
as3 = 50
as4 = 0
ElseIf Stars = 4 Then
cr = 68
as1 = 40
as2 = 50
as3 = 60
as4 = 70
ElseIf Stars = 5 Then
cr = 87
as1 = 50
as2 = 60
as3 = 70
as4 = 80
End If

If ASCENStart = 1 Then
EL = 0 'Extra Levels for Starting level
ElseIf ASCENStart = 2 Then
EL = as1
ElseIf ASCENStart = 3 Then
EL = as2 + as1
ElseIf ASCENStart = 4 Then
EL = as3 + as2 + as1
Else
EL = 0
End If
SL = EL + StartLev 'Start Level for Formula

If ASCENEnd = 1 Then
ELE = 0 'Extra Levels for Ending level
ElseIf ASCENEnd = 2 Then
ELE = as1
ElseIf ASCENEnd = 3 Then
ELE = as2 + as1
ElseIf ASCENEnd = 4 Then
ELE = as3 + as2 + as1
Else
ELE = 0
End If
EL = ELE + EndLev 'End Level for Formula

OffR = 18 ’ Offset rows to Get to start level

TotExp = Application.Sum(Range(Cells(SL + OffR, cr), Cells(EL + OffR, cr)))

TotFood = Application.Sum(Range(Cells(SL + OffR, cr + 1), Cells(EL + OffR, cr + 1))) 'Bad Estimate
TotFood1star = Application.Sum(Range(Cells(SL + OffR, cr + Foodoffset), Cells(EL + OffR, cr + Foodoffset)))
TotFood2star = Application.Sum(Range(Cells(SL + OffR, cr + Foodoffset + 2), Cells(EL + OffR, cr + Foodoffset + 2)))
TotFood3star = Application.Sum(Range(Cells(SL + OffR, cr + Foodoffset + 4), Cells(EL + OffR, cr + Foodoffset + 4)))

Cells(7, 7) = TotExp
'Cells(8, 7) = TotFood
Cells(9, 10) = TotFood1star
Cells(10, 10) = TotFood2star
Cells(11, 10) = TotFood3star
End Sub


#25

It also does not render the exponential plots - but they could be easily created.

Also here is a sheet with the same format for Troop cost.


#26

And the macro code for troop X* from level A to B.

Sub calcTroops()
'From input data in BLUE
StartLev = Cells(1, 8)
EndLev = Cells(2, 8)
Stars = Cells(3, 8)

'BASES
expc = 4 'Column of EXP data
f1c = 11
f2c = 12
f3c = 13
f4c = 14

If Stars = 1 Then
EC = 0 'extra columns

ElseIf Stars = 2 Then
EC = 13
ElseIf Stars = 3 Then
EC = 26
ElseIf Stars = 4 Then
EC = 39
End If

OffR = 12 ’ Offset rows to Get to start level
SL = StartLev
EL = EndLev

TotExp = Application.Sum(Range(Cells(SL + OffR + 1, expc + EC), Cells(EL + OffR, expc + EC)))
TotFood1star = Application.Sum(Range(Cells(SL + OffR + 1, f1c + EC), Cells(EL + OffR, f1c + EC)))
TotFood2star = Application.Sum(Range(Cells(SL + OffR + 1, f2c + EC), Cells(EL + OffR, f2c + EC)))
TotFood3star = Application.Sum(Range(Cells(SL + OffR + 1, f3c + EC), Cells(EL + OffR, f3c + EC)))
TotFood4star = Application.Sum(Range(Cells(SL + OffR + 1, f4c + EC), Cells(EL + OffR, f4c + EC)))

Cells(4, 7) = TotExp
Cells(5, 10) = TotFood1star
Cells(6, 10) = TotFood2star
Cells(7, 10) = TotFood3star
Cells(8, 10) = TotFood4star

End Sub


#27

This thread rook roook rookie


#28

Well done
But If you could send me the excel file itself using line app or any other app and I’ll upload it so no need to amend Marcos
If possible inform me how to communicate


#29

I am interested in:

worse case scenario ( one, non-matching color, 1* hero leveling ) from 5* 1.1 to 5* 4.80

a standard matching color scenario ( one matching 1* hero and one matching 2* hero - total of two - leveling ) from 5* 1.1 to 5* 4.80

Has anything else been done to make the above topic spread sheet, or any other user friendly format, available?


#30

I do not have an online spreadsheet, but here is a table with the values that you can use:

all: I may have these values wrong, but fairly certain most are right.
“NG” = “not good” in my shorthand. In other words, not the right color. Trainers are only listing as “in color”

The column I had “cards” is to be filled out, with the last column being the # XP * Cards.

5* Ascension XP
Tier Cum To Next
1 26810
2 75265 48455
3 180115 104850
4 353599 173484
FEEDER XP Value Cards XP * Cards
1* (ng) 150 0
2* (NG) 390 0
3* (NG) 630 0
4*(NG) 900 0
1train 1200 0
2train 1800 0
3train 3000 0
1* 180 0
2* 468 0
3* 756 0
4* 1080 0
TOTALS 0

Ascend Levels?
pinned #31

#32

@DJQuixo , I Could have used all of that information a while ago. Thanks for posting it now though. My tables are nearly done.

Completed with help from Freakshow, Eddard, Prophet of Loss, Gentoo and Luiz from King of the North; Shadow from Shade; JeeDee & Censure from Mixed Nuts - Extra Spicy and probably a few others I cannot recall right now.


#33

Hi Gryphonknight,
You may have gotten your answer already - but here is a summary:

The tan is the exp of a different color hero fed to another hero.
The Diff Color and Same Color are the total number of respective heros needed to reach the 353,559 exp for a 5 Star from Level 1.1 to 4.80.
The Red 22M, 8.6M and 5.3M are pretty sound estimates of the food required for each hero.


#34

Here is a 4 star screen shot same data.

and 3 Star Table:


#35

The amount of experience required for each new level follows a different quadratic equation for each ascension of each star hero. So with just a few data points - say 5 or 10 to be safe, of empirical data for heros, an equation can be fit and the experience curves can be generated. Then you simply sum up the proper range of the formulas that you want to calculate. The formulas were presented (in a poor text form) above. I hope you didn’t manually input all the data by hand!

10+20 = 30 total levels 1 Star
20+30+40 =50 2 Star
30+40+50 = 120 3 Star
40+50+60+70 = 220 4 Star
50 +60+70+80=260 5 Star
= 680 total levels for All ascensions all stars

I noticed your values are different from mine by about 3 to 5 EXP total points. Perhaps I have some rounding errors…


Shout out to our Veterans of old
#36

This is cool.

So if you are using Extra low cost training, with the occasional Extra fast ( RT 11 ) and Uncommon ( RT02 ) it will be approximately 15.45M ( the average of the two ) for different color and approximately 12.9M for same color.

==Per month==

Approximate 3*, 4*, 5*
2.5x ( to 3.0x ), 9.5x ( to 11.43x ), 54x ( to 64.89x )

==Per Year==

Approximate 3*, 4*, 5*
30x ( to 35x ), 114.3x ( to 133.35 ), 648.9x ( to 757.05x )

NOTE: these estimates will be off depending on play style since watchtower must be emptied every 12.8 hours and farms must be emptied every 21.8 hours for maximum food production.

==Food==

==Per 30 days==

Watchtower
14321 per hour x 24 hours x 30 days = 10,311,120 food

9x Farms
9x 5300 per hour x 24 hours x 30 days = 34,344,000 food

Total food = 44,655,120 per 30 days
or 2.8x to 3.5x 5*4.80

==Per 365 days==

Watchtower
14321 per hour x 24 hours x 365 days = 125,451,960 food

9x Farms
9x 5300 per hour x 24 hours x 365 days = 417,852,000 food

Total food = 543,303,960 per year
or 35x to 42x 5* 4.80

==Ratios==

22,384,007 / 5,874,534 = 3.81x ( ratio if leveling 4* instead of 5* )
22,384,007 / 1,034,417 = 21.63x ( ratio if leveling 3* instead of 5* )

==Building storage==

Watchtower 184,000 / 14,321 = 12.84 hours of storage

Farm 116,000 / 5300 = 21.88 hours of storage


[Play style] When to level your first rainbow 5* team based on Hero XP from training camps, team depth, team versatility, war, and upcoming class update- Updated with food costs
#37

Hi Thrashina,
Can you share the link to the spreadsheet?


#38

You folk’s are absolutely amazing, if I could get you all together and steal you from your Job’s I would go back to work for the IRS and we would rewrite the entire IRM and finally fix the system, at least the tax part.


#39

#40

I got it. Thank you thrashina