U
uw805
Please help with this formula for a baseball scoring spreadsheet:
Sample row:
Columns: A B C D E F G H I J K L M N O P Q R S T U
Row 1: Team A 0 1 0 5 0 0 1 0 0 Team B 0 0 2 1 0 2 0 0 0
The above row in an example of the scoring, by innings, in a baseball
spreadsheet. Cols B-J represent innings 1-9 for Team A, and L-M represent
innings 1-9 for team B. I need a formula that will display (in cell U1) the
biggest lead that team A had over team B at any point in the game. Assuming
Team A went first, the biggest lead they had would be 4. For reference, here
is the score of the game at the end of each half-inning:
Team A - Team B (team A's lead)
0-0 (0)
0-0 (0)
1-0 (1)
1-0 (1)
1-0 (1)
1-2 (-1)
6-2 (4)
6-3 (3)
6-3 (3)
6-3 (3)
6-3 (3)
6-5 (1)
7-5 (2)
7-5 (2)
7-5 (2)
7-5 (2)
7-5 (2)
7-5 (2) Final Score
I already have a formula that would calculate this, but it is too long:
=Max(Sum(B1:B1)-Sum(0),Sum(B1:B1)-Sum(L1:L1),Sum(B1:C1)-Sum(L1:L1),
Sum(B1:C1)-Sum(L1:M1),Sum(B1
1)-Sum(L1:M1),....,Sum(B1:J1)-Sum(L1:T1))
This works correctly, but since it has to account for 18 half-innings, it
has 18 comparison values. My actual sheet has room for 20 innings, so the
formula would have 40 calculation elements. Furthermore, the cell references
are actually 6 characters (ie DC4577), which makes it even longer. Is there
a simpler way to do this using an array formula or a sumproduct formula?
Thanks for helping...
Sample row:
Columns: A B C D E F G H I J K L M N O P Q R S T U
Row 1: Team A 0 1 0 5 0 0 1 0 0 Team B 0 0 2 1 0 2 0 0 0
The above row in an example of the scoring, by innings, in a baseball
spreadsheet. Cols B-J represent innings 1-9 for Team A, and L-M represent
innings 1-9 for team B. I need a formula that will display (in cell U1) the
biggest lead that team A had over team B at any point in the game. Assuming
Team A went first, the biggest lead they had would be 4. For reference, here
is the score of the game at the end of each half-inning:
Team A - Team B (team A's lead)
0-0 (0)
0-0 (0)
1-0 (1)
1-0 (1)
1-0 (1)
1-2 (-1)
6-2 (4)
6-3 (3)
6-3 (3)
6-3 (3)
6-3 (3)
6-5 (1)
7-5 (2)
7-5 (2)
7-5 (2)
7-5 (2)
7-5 (2)
7-5 (2) Final Score
I already have a formula that would calculate this, but it is too long:
=Max(Sum(B1:B1)-Sum(0),Sum(B1:B1)-Sum(L1:L1),Sum(B1:C1)-Sum(L1:L1),
Sum(B1:C1)-Sum(L1:M1),Sum(B1
1)-Sum(L1:M1),....,Sum(B1:J1)-Sum(L1:T1))This works correctly, but since it has to account for 18 half-innings, it
has 18 comparison values. My actual sheet has room for 20 innings, so the
formula would have 40 calculation elements. Furthermore, the cell references
are actually 6 characters (ie DC4577), which makes it even longer. Is there
a simpler way to do this using an array formula or a sumproduct formula?
Thanks for helping...