HELP: function identify cell of a certain criteria and subtract it from a specific cell

Background
I have been working on a simple net expected point success metric for NFL games, and I have run into a technical problem.


I want to subtract the score at play_i from the score at the half


Equation 1
Score_half_j-Score_ij


Picture 1 shows the relevant variables. The following variable names are marked in yellow: the points before a play_ij(ptso, where i is the play id and j is the half id), points at half-time_j (schalf, where j is given by halfid), and the relative point metrics (RELSCO, the data is this column is just to illustrate what I aim to achieve)

Picture 1: screenshot

IQ3SLrQ.jpg



I have an unique indicator for the half (j), I have an dummy for last play of the half and its corresponding score.


Question


My issue is the following:


Since the length of a half (in terms of the amount of attempts by an offence) differs from half to half, I need to make excel/stata identify the correct half time score to include in equation 1 (Score_half_j)

I.e. I need the software to scan the column for half time scores and find one with the same half id as play i.


I think this would involve some kind of lookup/find function with IF commands for the half ids being equal. However, as I am not an expert on excel I do not how to approach this issue.


It would be great to hear some input on how I might solve this.


Kind regards,


Magnus

Share
Quick reply to this message Reply Reply With Quote Reply With Quote Multi-Quote This Message