Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
uw805
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Locking portions of a formula tiggatattoo Excel Worksheet Functions 2 June 5th 06 04:51 PM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 05:30 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 05:18 PM
Creating a check mark box MarthaSue Setting up and Configuration of Excel 18 April 28th 05 12:31 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 04:37 PM


All times are GMT +1. The time now is 09:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"