Excel 2003: Automatically Changing Reference Cells for a Sum?

[mathias1979]

I'm basically doing a time weighted average of a monthly variable...using a formula like:

SUMPRODUCT(A2:A9,B2:B9)/SUM(B2:B9)

where column A has my monthly averages, and column B is the number of days in each month.

The spreadsheet gets added to each month, and I'm trying to automate it so that I don't have to manually update the formual above to account for an additional row each month.

Is there an easy way to do this? Intuitively, I would use ROW(A10) to get the row number where the average resides, then have the summation start at A2 and continue for ROW(A10)-1 rows. But is something like this possible in Excel?

 

[mathias1979]

Follow up...

In addition to a cumulative average, I'm also doing a average of the prior 12 months. For the prior 6 months I realized I can just use the offset function as so:

OFFSET(SUMPRODUCT($A$2:$A$7,$B$2:$B$7),ROW(A10)-8,0)/OFFSET(SUM($B$2:$B$7),ROW(A10)-8,0)

This will offset my initial average by additional row each time I add a row so that I'm always averaging the last 6 rows (months).

Essentially I need to do the same thing with the cumulative average, but instead of offsetting the entire formula, I only want to offset the 'ending' cell, while keeping the 'beginning' cell (A2) fixed.

I know I'm thinking aloud here, just wondering if there's a way to use the offset function to accomplish the cumulative average.

 

[SkipVought]

Hi,

As a matter of practice, I seldom put aggregations BELOW a table, as table rows change. Rather I put the aggregations ABOVE the table, with at least ONE EMPTY ROW separating the table for the aggregations.

Secondly, I almost always use Dynamic Named Ranged, that adjust when the table rows increase/decrease.

Check out the Data > List - Create List feature. Two things that I like about this feature are that FORMULAS are automatically added to new rows as data is added and the Named Ranges are adjusted as data is added/deleted.





Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mathias1979]

Alright, I just answered my own question. I didn't realize you could use the OFFSET function within another function. So I knew you could do SUM(OFFSET(A2:A9,2,0)), but didn't realize you could also do SUM($A$2:OFFSET(A9,2,0)). So I see I can just use a variation of the formulat in my second post (recognizing that I erred in reversing the placement of OFFSET & SUMPRODUCT) to accomplish my moving cumulative average.

I guess sometimes you just need to type this stuff out to get the creative juices flowing. Hopefully no one wasted too much time reading/responding to my post.

 

[mathias1979]

Skip,

Thanks for the additional input! I will give it some thought.