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)
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)
A leader of Fire Clan