Formulas

Explanation

=IF(C7=0,0…

If the previous cell C7 = 0, then all subsequent Red Force cells equal zero as no Red Forces remain, else…

IF(E7=0,C7

If the Blue Force E7 = 0, then the Red Force equals its previous amount, C7. With the Blue Force destroyed, the Red Force no longer changes – no gains or losses since the opposing force is no longer a threat.

IF((C7-$C$3/100*E7-$C$4/100*C7+$C$5)<1,0

But if the Blue Force E7 ≠ 0, then the Red Force equals its previous numbers, C7, minus the effect of the continuing battle – that it to say, the Red Force continues to experience battle losses proportional to the size of the Blue Force, -$C$3/100*E7, and non-combat losses continue to erode the Red Force too, -$C$4/100*C7, and reinforcements bolster the red Force’s size, +$C$5. After considering these changes, if the Red Force size is less than 1, the Red Force is set to zero, as a fraction of a troop/soldier cannot exist/fight, hence, the condition of if < 1 then 0 or in Excel, < 1, 0.

C7-$C$3/100*E7-$C$4/100*C7+$C$5

Now if the Blue Force E7 ≠ 0, and the Red Force is able to fight ( > 0 ), then the Red Force troop size is this resulting value.