Help with ranges that change size

[baseball]

I have a spreadsheet that dynamically calculates a team's expected winning percentage based on the strength of its remaining opposition. When I update the won-lost records for each team in the league, each team's strength is automatically recalculated.

My problem lies with having to manually alter the arrays every day. Every time a team plays a game, then there are fewer games left to play.

I use one table to record the wins and losses for each team. That data is used to calculate the log5 number for that team. The table aslo includes the game-by-game opponents for the team:

Team W L log5 OPP1 OPP2 OPP... OPP162
ANA 88 55 .800 CLE CLE ... NYA

There are 30 teams, but I'm just showing one to save space.

I set up the next table as a vlookup to substitute the log5 number for each opponent that a team has

Team OPP1 OPP2 OPP... OPP162
ANA .405 .405 ... .840

I use the average of the log5 numbers for OPP1, OPP2,...,OPP162 [SUM(E30:FJ30)/COUNT(E30:FJ30)] as the basis for determing the expected won-lost percentage, but after a game is played the remaining schedule is OPP2,OPP3,...OPP162 [SUM(F30:FJ30)/COUNT(F30:FJ30)]and the array has to be adjusted accordingly. Because every team does not always play every day, and they don't necessarily play the same number of games on a given day, I can't simply change one formula and copy it down.

Is there some way that I can handle this more easily? As an example if all three of these teams played today, I would need to change two formulas for each team

A... B....... C.................... D..................
Team E{W}.... Games remain x D..... Average log5 value remaining
ANA =B35+C67 =D67*COUNT(AB67:AT67) =AVERAGE(AB67:AT67)

In C and D, the ranges are dynamic, and after today's games (assuming that the team played) would need to be updated to:

A... B....... C.................... D..................
Team E{W}.... Games remain x D..... Average log5 value remaining
ANA =B35+C67 =D67*COUNT(AC67:AT67) =AVERAGE(AC67:AT67)


This is very tedious to do for thirty teams, and very much open to error.

I hope this is clear, thanks,
Cliff

 

[Corticus]

Used named ranges:

select current data range,
click insert,
click define
type in name,
click add,

Now refer to the named range in your formulas instead of an explicit range
Sum(A1:A20) would be Sum(RangeName)

Now the range should increase as the size of
your data set increases.

HTH,
Corticus

 

[baseball]

On 2002-09-10 07:13, Corticus wrote:
Used named ranges:

select current data range,
click insert,
click define
type in name,
click add,

Now refer to the named range in your formulas instead of an explicit range
Sum(A1:A20) would be Sum(RangeName)

Now the range should increase as the size of
your data set increases.

The data set is constant; the portion of it that I use decreases on an irregular basis, so I don't think what you suggest is the answer.

Dynamic ranges are what I need, but I cannot simply delete columns or rows because that might delete games that were not played due to vagaries of the schedule and weather.

And, since my formulas call for averages and counts, I run into problems if I simply delete the contents of cells no longer needed.

At the moment, the only way I can think of handling this is to put each team on a separate worksheet, then I could have dynamic ranges and simply delete columns as games have been played. Having 31 worksheets (one for Summary) when one should do does not really turn me on.

Thanks,
Cliff

 

[Vlad]

cliff,

even without lookin at the details, it seems like you do indeed need dynamic ranges

try working with offset function.

Here's how: (courtesy Rodney Powell)

=OFFSET(MySheet!$A$1,0,0,COUNTA(MySheet!$A:$A),1)
The first argument is the cell on which you want to anchor it. Everything else will be set relative the this cell address. Typically, you will want it to be either the header for the first field in your source data table or its first record.
The second argument indicates how many rows to move from the anchor address to begin the range. In this case, we used 0 which would include my header row. If we substituted 1 then the header row would be skipped and the range would begin on row 2 of the table.
The third argument indicates how many columns to move from the anchor address to begin the range. In this example we used 0, meaning to begin the range on the same column as the anchor address provided in the first argument.
The fourth argument tells how many rows the range should extend. They key here is to substitute the COUNTA function for your primary field, instead of hardcoding a value. This way if you add or remove items from that field, the range will grow or shrink accordingly. You also may need to subtract 1 from the COUNTA result to account for the elimination of a header row. It is important that you do not have any superfluous data beneath the table range you intend to evaluate.
The fifth, and final, argument is how many columns wide you want for the dynamic range to be. In our example, this range includes only a single column, therefore the argument provided was simply 1.

 

[baseball]

On 2002-09-10 07:00, baseball wrote:
I have a spreadsheet that dynamically calculates a team's expected winning percentage based on the strength of its remaining opposition. When I update the won-lost records for each team in the league, each team's strength is automatically recalculated.

My problem lies with having to manually alter the arrays every day. Every time a team plays a game, then there are fewer games left to play.
[...]

I found the solution to my problem...adn it was embarrasingly simple.

I have one table that I update to show the current won-lost records for each team and their calculated log5 values.

I have a second table to show the remaining opponents for each team:

Team-Opponents
BOS--BAL BAL BAL TOR etc.

The next table is a vlookup that find the log5 value for each of the team's remaing opponents:

Team-Opponents' log5
BOS--.347 .347 .347 .403 etc.

These are dynamic. i.e., they change as team records change.

I first tried simply deleting teams from the second table after that game was played, but that blew up the rest of the calculations.

I finally realized that if I deleted the numbers from the third table, Excel adjusted things properly.

This entire problem owuld have been a lot easier to describe (and probably to solve) if the add-in for displaying worksheets in this forum would work on my system, but for some reason, my combination of Win98 and Excel97 is not compatable with it.

Thanks to all who took the time to try and help me.

Cliff