how to do sequential analysis (+and incremental counting) ?

[gorimaa]

hello every body

i would like to know if there is a way to analysis occurence of words in a colomn in a sequentail way. it is little bit complicate so here an exemple i would like to achieve:

here us my colomn of two words "win" and "loose" coming in a random way that i input myself. lets say these are result of some connect 4 game's sets, were to first person to win a match is the one who achieved 3 won set in row. My goal is to able to count how many win and lost match throught the sequence of won/lost set in sequential order from top to bottom. i put manually the result on the right colomn (victory and defeat) and final total results at the bottom end , but i would like this to be done automatically:

SET: MATCHES:
win
loose
win
win = victory
win
win
win = victory
loose
loose
win
loose = defeat
win
win
win = victory
loose
win
win
loose
loose = defeat

total results of matches : victory: 3 / defeats : 2



is it possible to achieve that in excel ?

thank you so much



windows 10 - excel 2016 - beginner level

 

[ravi2628]

Hi,

Please Try This.

Example.xlsb
	A
1	win
2	loose
3	win
4	win = victory
5	win
6	win
7	win = victory
8	loose
9	loose
10	win
11	loose = defeat
12	win
13	win
14	win = victory
15	loose
16	win
17	win
18	loose
19	loose = defeat
20
21	Total results of matches :Victory:3/ Defeats:2
Sheet1

Cell Formulas
Range	Formula
A21	A21	=CONCATENATE("Total results of matches :Victory:",COUNTIFS(A1:A19,"*"&"Victory"),"/ Defeats:",COUNTIFS(A1:A19,"*"&"Defeat"))

Regards,
Ravi

 

[gorimaa]

Thank you so much for replying so fast

Sadly this not working , but i thing it comes from my explanation to be not clear enough:

- the "victory" and "defeat" i input was just here to clarify the sequential counting that is needed.

So mostly i would like the formula to sequentially analyse the colomn A1-A19 starting by A1 and counting how many "WIN" and "LOOSE" , stopping as soon as one of the two reached 3 .

exemple :
if 3x "WON" is reached before 3x "LOOSE" in the sequential counting (whether it could be 3x WON vs 0,1 or 2x LOOSE) then increment the "victory" by +1 (it would be perfect to be able to input the word "victory" or "defeat" next to the level where a match is won or lost but it is not mandatory as it could be too complicate).

Then put back counting to 0 in order start counting again for the next following match , if for exemple this time the counting sees "LOOSE" reaching 3 before "WIN" made it (again whatever WON reached 0 or 1 or 2 , the important is the first reaching 3) then increment the block "defeat" by +1.

In the hope it can helps

Cherrs