I want to count the number of losses in a row that my criteria have.

Question

I'm new to SQL and excel. I'd rather have the solution in excel if that's possible. Or both

Some of my columns are: Time date, cloth number, Finish Position, Win(my own column where if finish position is 1 it says "winner", and if not it says "null")

So the cells of my last 2 columns look something light this:

Finish pos      Win

1                     winner

4                      null

3                      null

1                    winner

I want count how many losses there are in a row/sequence all the way down through the data. If there were 5 nulls in a row, I want there to be a number 5 in the next column over, beside the 5th null(it doesn't have to be beside the 5th null, but only one "loss amount" per sequence). Here's a sideways example to save space:

.winner, winner, null, winner, null, null, null, null, null, winner, winner, winner, null, null, winner, null, winner, null, winner, winner.

 .          ,           ,   1  ,            ,       ,      ,      ,       ,  5   ,           ,            ,           ,       ,  2   ,           ,  1  ,            ,   1  ,           ,          .

Once that's done in a spreadsheet of say 5 years of data, I'd like to go year by year and be able quickly find out how many times in a year there were =/> 4 losses in a row. So something like "2013-2014 how many values in column X are >/=4"

Any help would be much appreciated.

Answer 1

You can do this in Excel very easily:

Column A has win/loss in the form on [1,0]

Column B has a formula in cell B2 (assuing row 1 is your header) =IF(A2=A1,B1+1,1)

Copy down and it will give a running count of consecutive wins/losses

It can also be done using SQL and even easier in R if you know how to use that.

Comments

Thanks very much!