Win 8.1/Office 14
I have a worksheet with baseball inning-by-inning scoring on it with range names like innV1, innv2,...innH25, innH26 and WLT (for Win/Lose/Tie). I want to determine the team's winning percentage when it scores first (by inning). For example, if the visiting team (which bats first) has not scored through six innings of play, I want to determine what percentage of games the home teams when in that situation.
When the visiting team scores in the 1st inning, it is a trivial matter to determine but the method I found to determine other instances is a bit more problematical. I wanted to used sumproduct as in:
SUMPRODUCT(((innV1+innV2+innV3)=0)*((innH1+innH2)=0)*(innH3>0)*(WLT="H")) which will tell me how many games the home team won when they scored in the bottom half of the third inning of a scoreless game. However, this gets unwieldy as the scoring gets later in the game.
The question I have is whether there is some way to simplify this as I am not sure that Excel would accept a formula that would cover 26 innings (the length of the longest game). I could add a couple more tables with teh cumulative scores by innings for each team but that would add another 26 range names. Suggestions?
I have a worksheet with baseball inning-by-inning scoring on it with range names like innV1, innv2,...innH25, innH26 and WLT (for Win/Lose/Tie). I want to determine the team's winning percentage when it scores first (by inning). For example, if the visiting team (which bats first) has not scored through six innings of play, I want to determine what percentage of games the home teams when in that situation.
When the visiting team scores in the 1st inning, it is a trivial matter to determine but the method I found to determine other instances is a bit more problematical. I wanted to used sumproduct as in:
SUMPRODUCT(((innV1+innV2+innV3)=0)*((innH1+innH2)=0)*(innH3>0)*(WLT="H")) which will tell me how many games the home team won when they scored in the bottom half of the third inning of a scoreless game. However, this gets unwieldy as the scoring gets later in the game.
The question I have is whether there is some way to simplify this as I am not sure that Excel would accept a formula that would cover 26 innings (the length of the longest game). I could add a couple more tables with teh cumulative scores by innings for each team but that would add another 26 range names. Suggestions?
