I have a long spreadsheet with periodic subtotals in columns d and e. I put a figure in column g on each subtotal line. I input this formula
=((d2/d$9999*.5)+(e2/e$9999*.5))*g$9999 on the first row of column f and drag it to the last row of column f. Then I visually identify a subtotal line number and change 9999 in each row to the subtotal line number. For example, if a subtotal is on line 11, I highlight column f, rows 1 through 11, do an edit/replace to change 9999 to 11. If the next subtotal is on line 15, I highlight rows 12 through 15, do an edit/replace to change 9999 to 15. I do this for each subtotal block throughout the entire worksheet. Can I automate this so the program identifies the next subtotal line number and allocates the figure in column f to each applicable row that relates to its subtotal?
calculate rows based on periodic subtotals
-
-
-
Blaine,
Are the subtotal lines evenly spaced?
If not is there any common text on the subtotal lines that can be used to identifiy it? -
Quote from Kieran
Blaine,
Are the subtotal lines evenly spaced?
If not is there any common text on the subtotal lines that can be used to identifiy it?The subtotal lines are not evenly spaced. There may be as few as one line before a subtotal and then 20 or more lines leading to the next subtotal line.
The only text on the subtotal line is the figure I enter in column g, which is a different number for each subtotal line, and the wording the subtotal routine puts in column b- a job number such as 04-1234 with the word "Total". Thanks for reviewing my question.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!