Recalculate formula

[heather]

Hi; I have a very large spreadsheet; with eg. from 2-4000 rows of data for
each household in each suburb. I have separated the suburb records by writing
a macro that inserts two lines between each separate suburb (ie. between
Suburb AAA and Suburb BBB). In those two lines, I wish to calculate the Sum,
Average and Median of the data in column B (A is the suburb name). I filter
the suburb for blanks, and then paste and copy down the formula into all 3
cells of the blank rows (all formulas refer to the data in column B).
However, I have a weird problem with the formulas, in that the formula does
not automatically calculate the column of data from the first record below
the blank line; sometimes it works, sometimes it reverts to using the cell
reference of the first record, and sometimes it only works if I have more
than about 1000 rows). Calculate is set to automatic, and this problem is the
same in 2003 and 2007. I can do this by manually adjusting the cell reference
each time, but it is a pain. Any help much appreciated.

 

[heather]

edit: once i have calculated the totals etc; i then delete all the individual
records, and move the totals row to the top; eventually giving me a
spreadsheet with just
suburb 1 - sum, average, median
suburb 2 - sum, average, median
suburb n - sum, average median;

....as the original data is in about ten enormous spreadsheets and I only
want to do stats on the summary data.

 

[Bernard Liengme]

Why bother with the blank rows?
Use formulas like =SUM(IF(A:A="Suburb C",B:B,""))
1) this is an array formula, so commit it with CTRL+SHIFT+ENTER
2) use the real name for suburb or use a cell reference
3) this approach works with SUM, AVERAGE and MEDIAN in my small test data
4) When ready use Copy followed by Paste Special (Values) to convert
formulas to values before deleting the data
best wishes