Discerning between success and fail in each row where player has multiple scores

[BrettChitty]

Hi,

I'm attempting to write a series of formulas to little success.
I'm currently analysing records from a MMO game I play to monitor my performance as well as that of my colleagues in my alliance.

In the attached file,
First column: "War number". (main file has over 30 wars)
Second column: "Battle number" in that war (uses countif based on column 1, but not relevant here)
Third column: Unique battle ID regardless of war number. As there are 360 battles per war, I have over 10000 entries.
Columns D & E, is just a vlookup of the team's name. Not relevant here either.
Columns F is the attacker's name, but I didn't start recording this until battle 7, but again, not needed here.
Column G is the attacker's attack number in that particular war (another countif)
Column H & I serves the same purpose as F & G, but from the defender's perspective.
Column J is the score of that particular battle.
Column K is the adjusted result as scores are inflated if the war had less than the maximum number of participants.

What I'm trying to do:
Column L: To tell me what number "takedown effort" this is on the defence.
In the first battle on defender "but" (row 2), then it would be a first attempt, because there is no earlier attempt on that defender in this particular war.
Every first attempt on a FRESH defence is called a "one shot attempt", which is what I'd like to show in column M.
In row 2, because the score was over 40 (50 points in column J), then it means the defence was SUCCESSFULLY taken down.
This is the easy part for me.
The subsequent rows is where I'm having trouble.

After each successful take down of the defence, the defence is spawned fresh again.
Hence in column L, we see that this defence is now on its second spawn.

In row 3, you can see the attempt to take down that fresh new defence only yielded 22 points, and as it was less than 40 points, it was not enough to take down that defence. At least some of it is still standing.
This attempt is still a "one-shot attempt" because it was on a fresh new defence, but registered as failed because it did not achieve the minimum 40 points.

In row 4, you can see that there was an attempt to to finish off what was left over of the defence in row 3. because "But's" defence had already lost 22 points in row 3, there was only 18 more minimum required to require a complete take down.
Because a "one-shot" is only possible on a fresh new defence, Row 4 cannot be considered a "one-shot" we refer this this attempt as "clean up." So we put this attempt down as a "1st Clean Up"
The attacker scored 29 points in row 4, so if you combine this row with the previous failed one-shot on row three, and add the scores up together, we have a total of 51. This is over the 40 point threshold, and therefore the defence successfully is taken down. Hence the "success" in column N.

However if you look in rows 8 and 9, some defences are harder to take down, and requires more attempts to bring it down. So we have "2nd clean up" and 3rd clean up and so on. We've had battles where it reached a 7th clean up!

I've tried many different ways to get to this information automatically, but not quite getting there. I have the logic in my head, but not quite able to translate it into Excel's logic.
I couldn't quite get excel to recognise if it's a fresh defence, do not count the battles scores from previous attempts on the same defence in the same war.
However if you'd to take this on, It would be appreciated it you could come up with the formulas for me to get this info.

If you'd like bonus points, some defences total defences scores can vary between 46 and 58. if you can base the threshold based on the defence's maximum score, then I would no longer need the arbitrary figure of "40."
(bear in mind of rounding, occasionally a defence score can vary between 2 or 3 points per takedown, meaning, sometimes it takes the same defence, 50, or 51 points or 52 points.)

Thanks for reading at least, and I'm happy to reply here if anything is not clear or is confusing or if you need any clarification.
As an incentive, I might donate a few pounds/dollars to this work, especially if it's explained to me how your formulas work so I can learn from it and hopefully move me forwards in becoming a bit more competent in using Excel.

Best wishes.