Wednesday, January 18, 2012

yogi_Formulas For Specified Functions To Remain Valid Even If Rows Are Inserted And Or Deleted

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
user dpmacd said:
Automatically adjust for sum and average
I have a spreadsheet. Column A is employee name. Column B is total number of training hours. Rows 2-10 are current employees. Row 11 is "Total departmental training hrs" =SUM(B2:B10). Row 12 is "Average training hrs per employee" =AVERAGE(B2:B10).
As we add new employees (insert new row) in alphabetical order to Column A, is there a way to set the formula (or maybe a different way of inputting a new employee) to automatically adjust itself accordingly so that the SUM and AVERAGE reflect all current employees plus the new employee? So that every time a new row is added, B10 in the formulas goes up by 1..
As we will have multiple people managing this spreadsheet, it would be great if we didn't need to remember to adjust the formulas every time a new employee is added.
Thanks much,
following is a solution to the problem