Fire Clan IOU Multicalc

[GameInSky]

Complete fish support and update notes were added to the sheet!

[jiecut]

So recently I found on the card sheet for Next Milestone.

Say you have 500 cards, it'd say the next milestone would be 250 instead of 1000. And I was reminded by GameInSky about the bug if you had 0 cards, which is the same bug because you have 0 cards.


Cards Column O


=if(index($R$3:$R$14,match(M3,$R$3:$R$14))-M3=0, offset(index($R$3:$R$14,match(M3,$R$3:$R$14)),-1,0)-M3, offset(index($R$3:$R$14,match(N3,$S$3:$S$14)),1,0)-M3)


Current code checks if it's on a milestone and then makes a special case for it. But the problem was the -1 offset. It should really be Offset +1. Then you realize that the true and false statements are doing the same thing. It look likes the false is doing something difference by referencing something else for the match but it really does the same thing.


=offset(index($R$3:$R$14,match(M3,$R$3:$R$14)),1,0)-M3

So the formula can actually just be simplified to this.

It also means the P column can be fixed up since I don't think there's a case where column O is 0.

From =if(O3=0,"", if(O3>$D$9,"",O3-$D$9))

To =if(O3>$D$9,"",O3-$D$9)

So while this gets fixed there's some improvements that can be made.

* Sometimes a card pack can be used to get through multiple tiers. The calculator currently would say that the card pack is really inefficient for getting through 1 tier.


To implement number 1. In the P column instead of just subtraction you could add the cards, then do index match to find milestone then subtract to see how much you go over.

From =if(O3>$D$9,"",O3-$D$9))

=if(O3>$D$9,"",index($R$3:$R$14,match(M3+$D$9,$R$3:$R$14))-M3-$D$9)

[jiecut]

My numbers seem to be off quite a bit. I think it has something to do with lineage level being multiplicative not additive. Able to clear 2-30+ when calculator says 2-15. DPS is also off by quite a bit.

[k3rb3r05]

(08-17-2016, 08:16 AM)jiecut Wrote: So recently I found on the card sheet for Next Milestone.

Say you have 500 cards, it'd say the next milestone would be 250 instead of 1000. 

Why 750?  You don't get bonus dmg at 750 but at 1000.

[jiecut]

(08-18-2016, 02:59 PM)k3rb3r05 Wrote:

(08-17-2016, 08:16 AM)jiecut Wrote: So recently I found on the card sheet for Next Milestone.

Say you have 500 cards, it'd say the next milestone would be 250 instead of 1000. 

Why 750?  You don't get bonus dmg at 750 but at 1000.

The bug was that it'd thing the next milestone goal was 250. So it'd think you need -250 cards to go to the next milestone.

The correct milestone goal is 1000.

Basically the formula uses offset and it was looking at the wrong way.

[k3rb3r05]

Challenge data's conditional formating is screwy.

Edit 1 of them to RANGE "m6:u50" and FORMULA "=M6<$N$2". Delete the rest from each column

Whenever a new challenge is added or more levels, all you have to do is edit the range, once.

[JourneyOver]

(08-12-2016, 05:34 AM)GameInSky Wrote: New version with a much lighter ifnerno tier calculator is up!

(08-18-2016, 02:40 PM)jiecut Wrote: My numbers seem to be off quite a bit. I think it has something to do with lineage level being multiplicative not additive. Able to clear 2-30+ when calculator says 2-15. DPS is also off by quite a bit.

They was off a bit before lineage as well, but I do agree that the numbers are off a bit more than they used to be.

Currently Damage in-game is averaging between 2.37m to 2.5m dps (running 5 minutes for dps timer)
Damage on sheet says average is around 2.12m

Don't remember what it was looking like before lineage since I have already deleted that spreadsheet from my history.

[GameInSky]

Sorry! I had forgotten to add the damage boosts from lineage.

That's now fixed. I also simplified the lineage part of the sheet, since the game tells you how many days you need for your next one anyway.

[k3rb3r05]

DPS shot through the roof after latest fix.  MAIN!D8 changed from a 9 (old sheet) to a 13 (new sheet)

I am no genius in excel (as i told you a couple days ago in discord - brouwer's cards) but a simple workaround would be to keep MAIN!D8 formula as it was

Code:

=if(C8<4,0,if(C8<12,1,if(C8<20,2,if(C8<34,3,if(C8<56,4,if(C8<88,5,if(C8<132,6,if(C8<190,7,if(C8<240,8,if(C8<324,9,if(C8<428,10,if(C8<593,11,if(C8<782,12,if(C8<956,13,if(C8<1160,14,if(C8<1389,15,if(C8<1643,16,if(C8<1922,17,if(C8<2226,18,if(C8<2635,19,20))))))))))))))))))))

AND change formulas 

MAIN!V10 to:

Code:

=Round((1.1*C43*(1+D6+'Asc & Cha'!E4)*(1+D7)*(1+C47))*(1+R4)*(1+D8*0.02))

MAIN!V11 to:

Code:

=Round((C43*(1+D6+'Asc & Cha'!E4)*(1+D7)*(1+C47))*(1+R4)*(1+D8*0.02))

MAIN!V12 to

Code:

=round(C44*(1+D6+'Asc & Cha'!E7)*(1+D7)*(1+D8*0.02)*(1+C47)*(1+R7),0)

don't know what other changes you made that are affected by lineage , but this fixes the dps thigny

[JourneyOver]

(08-19-2016, 03:42 AM)k3rb3r05 Wrote: DPS shot through the roof after latest fix.  MAIN!D8 changed from a 9 (old sheet) to a 13 (new sheet)

I am no genius in excel (as i told you a couple days ago in discord - brouwer's cards) but a simple workaround would be to keep MAIN!D8 formula as it was

Code:

=if(C8<4,0,if(C8<12,1,if(C8<20,2,if(C8<34,3,if(C8<56,4,if(C8<88,5,if(C8<132,6,if(C8<190,7,if(C8<240,8,if(C8<324,9,if(C8<428,10,if(C8<593,11,if(C8<782,12,if(C8<956,13,if(C8<1160,14,if(C8<1389,15,if(C8<1643,16,if(C8<1922,17,if(C8<2226,18,if(C8<2635,19,20))))))))))))))))))))

AND change formulas 

MAIN!V10 to:

Code:

=Round((1.1*C43*(1+D6+'Asc & Cha'!E4)*(1+D7)*(1+C47))*(1+R4)*(1+D8*0.02))

MAIN!V11 to:

Code:

=Round((C43*(1+D6+'Asc & Cha'!E4)*(1+D7)*(1+C47))*(1+R4)*(1+D8*0.02))

MAIN!V12 to

Code:

=round(C44*(1+D6+'Asc & Cha'!E7)*(1+D7)*(1+D8*0.02)*(1+C47)*(1+R7),0)

don't know what other changes you made that are affected by lineage , but this fixes the dps thigny

haha it didn't shoot through the roof for me, it's still lower than my DPS (running 5 minutes on dps timer) in game still :x

Current damage in-game is averaging between 2.35M to 2.55M
Current damage on sheet says average is around 2.27M