Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Baseball sumproduct/array formula?
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:D1)-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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking portions of a formula | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |