I am using Excel 2004 on my Mac to do some simple sum, averages and max calculations. The worksheet consists of several thousand rows and each set of rows that I want to calculate consists of a different number, ie, the first set of data might have 20 rows while the next sent 300.
For the first set, I insert a row with the formulas under each column to calculate and I include 4 empty rows below the formula row. Next, I copy and insert the 5 rows (1 with formula and the 4 empty) below the next set of data.
What I would like to happen--and I seem to recall one of the earliest versions of Excel doing this--is to have the newly inserted first row calculate the column data up to the last "blank" row in the previous set.
Example: Rows 1-20 have the first set of data. I put the formulas in row 21 and add 4 empty rows below this (so rows 21-25 are used). I then "copy" rows 21-25 and insert them under the next set of data, say rows 26-300. So my formula row is in row 301. What I would like is for the newly inserted row-301 to automatically "see" that I am trying to calculate the column data for rows 26-301, that is, without me having to put the formulas in again.
But this does not happen, of course. Instead it simply uses the formulas from the previous set of data. So instead of calculating the new set it only calculates the first 20 rows as was done in the first set.
I know I don't need four empty rows, but I use this number to visually help me see things easier on the worksheet.
This is harder to explain than I thought it would be. Hope it makes sense to some of you!
Bookmarks