Excel: Formula to ADD to last digit depending on scenario. Hope you can help me thanks!

[IMG]http://i61.tinypic.com/15ft92w.jpg[/IMG] Hi Guys, I need help in getting a formula right. I have attached a picture, FIRST column containing A's and B's. While SECOND column containing some numbers. I need a formula where the SECOND column can calculate get those numbers. I will write it as easy as possible to understand. Here are the rules for the SECOND column: *Step 1: If A happens in the FIRST column, we will write a "1" in the SECOND column. Step 2: For every A that happens we will +1 to the SECOND column column of the last digit. So in the next row another A happens so we +1 and the result will be "2" in the SECOND column. Step 3: Every time B happens we do two things. Either we wait for two B to happen then we +1 to the latest digit. So once we got a B there is a blank as we are waiting for either two B or one B and one A. Step 4: As you can see, another B appeared so there are two consecutive Bs, therefore we +1 to the latest digit, giving us a "3" now. *[I]Every time there is two consecutive Bs, the latest digit will reset to 1[/I] and we start from Step 1. Following there is a A so the count in SECOND column is 1 again. Next we have a B, here we can look at Step 3, whenever we got a B we wait for either two consecutive B's or a B followed by A, as you can see here A appeared after this B. From here we can look at Step 2, "For every A that happens we will +1 to the SECOND column of the last digit" so now the digit will be +1 giving us a "2". --------------------------------------------- The rules are basically these, hope you guys can help me with a formula that I can copy and paste down the SECOND column. I hope I can write this clearer, if you have any questions I will gladly answer. Hope this is not too much to ask its for a project of mine thanks!

This question generated 10 answers. To proceed to the answers, click here.

This thread is current as of November 27, 2014.

For more resources for Microsoft Excel