Calculating Lead Changes in Spreadsheet

bmv505

New Member
Joined
Sep 28, 2022
Messages
24
Office Version
  1. 365
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.

1704544767357.png
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
It would be nice to have actual data pasted in, instead of a picture, for testing. But try this:
Excel Formula:
=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.
 
Upvote 0
@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
LMN
1StatusLead Changes
2Tied1
3Lead
4Tied
5Lead
6Tied
7Trail
8Tied
9Trail
10Trail
11Trail
12Trail
13Trail
14Trail
15Tied
16
Lead Change
Cell Formulas
RangeFormula
N2N2=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)))
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,223,887
Messages
6,175,199
Members
452,617
Latest member
Narendra Babu D

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top