Calculating Lead Changes in Spreadsheet

[bmv505]

I have a spreadsheet with the scoring plays from several American Football games (Game 1 through Game 13). I want to calculate the number of lead changes.

1. The game starts off tied 0-0. When someone scores first, there is not a lead change (tied -->LA lead).
2. A lead change occurs is when one team has had a lead and the other team takes a lead (either "LA lead -->Opp lead" OR "LA lead --> tied -->Opp lead")

For example, in the photo attached, one lead changed occurred in game 2 at 34:15 when Opp took lead, and that is it.

 

[6StringJazzer]

It would be nice to have actual data pasted in, instead of a picture, for testing. But try this:

=COUNTIFS($K$2:$K$100,"<0",$K$3:$K$101,">0")+COUNTIFS($K$2:$K$100,">0",$K$3:$K$101,"<0")

This counts the number of times the Difference flips from negative to positive plus the number of times it flips from positive to negative.

100 is the next-to-last row of data, and 101 is the last row of data. Update the formula to reflect the maximum possible rows of data you expect to have.

 

[Peter_SSs]

@6StringJazzer
Jeff, for me that returns 0 for the sample data when, as stated by the OP, there is 1 lead change shown.

@bmv505
See if this does what you want

24 01 07.xlsm
	L	M	N
1	Status		Lead Changes
2	Tied		1
3	Lead
4	Tied
5	Lead
6	Tied
7	Trail
8	Tied
9	Trail
10	Trail
11	Trail
12	Trail
13	Trail
14	Trail
15	Tied
16
Lead Change

Cell Formulas
Range	Formula
N2	N2	=LET(s,CONCAT(IF(L2:L100="Lead",1,IF(L2:L100="Trail",2,""))),2*LEN(s)-LEN(SUBSTITUTE(s,12,0))-LEN(SUBSTITUTE(s,21,0)))

 

[6StringJazzer]

@6StringJazzer
Jeff, for me that returns 0 for the sample data when, as stated by the OP, there is 1 lead change shown.

I misunderstood how he was defining "lead change". My formula missed a lead change if there is an intervening tie, which is what is happening in the sample data.