You might be there already, but in Access it is as simple as creating a
query on the table, click the summation symbol (fancy 'E' [Sigma] icon),
Group By the dimensions (LotPlan, 'reading period'), and Average the
value 'water consumption'.

heather wrote:
Thanks, after posting, I did as you said - ported it to Access, and it is
working fine! Still need to figure out some things re averaging in Access,
but shouldn't be an issue. (I couldn't do a pivot table in 2003 or 2007
because of the row limitation...)
thanks again

"smartin" wrote:

heather wrote:
I have a number of very large datasets of household water consumption which I
want to combine so I have an average reading per house per year (eg I have
four spreadsheets per year, for an area (or subset of area, it is a city, and
some are VERY large)). Each row has an identifier, LotPlan, most of which are
unique, but there are some duplicates (where this is so, they refer to the
same parcel, so I can just average or sum the readings with no problem). I
have deleted all zero records.

What I want to do is run subtotals and average the Avg daily water
consumption per LotPlan (some have four readings, some may only have one).
However, Excel will not let me subtotal, as it gives me the end of data
error, which I figure is related to the size of the spreadsheet. What I was
doing was appending the data so I had one worksheet with all the information,
identified by LotPlan, and also by reading period; however, I cannot do this
either, as then it is more than 65K rows. Are there any ways I can get around
these problems in Excel 2003 or 2007? If not, can I do it in Access or SPSS
or other software? I have 4Gig RAM, although it is still very slow! Thanks
for any help.
A few thoughts...

In E2007 AFAIK you will not have this problem unless you have up around
1M rows. But, I do not have E2007 so cannot say for sure.

In E2003 you might be able to get subtotals adequately by using a Pivot
Table. Oh, maybe not since the consolidated data exceeds 64K rows.

Were it me hitting the limit, as it were, it would be time to port the
data to something more capable. Access is a logical choice. With the
consolidated data in one table Access can easily aggregate totals and
averages.