Update References in Formulas when Columns are Inserted and Rows are Skipped

[Arnold]

Good day,
In a sheet for tracking students and grades, assignments start at
column Y and are inserted to the right each day or as needed.
Currently, there have been 9 assignments-extending from col. Y to col.
AG.

I would like Excel to auto-update the references in formulas that are
in columns O, P, and Q:

Col O = SUMIF(Y13:AG13,">0",Y$10:AG$10) - this column is to sum the
total assignment points possible for a student.

Col P = (Y13+Z13+AA13+AB13+AC13+AD13+AE13+AF13+AG13) - this column is
to sum a student's points earned.

Col Q =(((Y13/Y$10)+(Z13/Z$10)+(AA13/AA$10)+(AB13/AB$10)+(AC13/AC$10)+
(AD13/AD$10)+(AE13/AE$10)+(AF13/AF$10)+(AG13/AG$10))*100)/
COUNT(Y13,Z13,AA13,AB13,AC13,AD13,AE13,AF13,AG13) - this calculates
the percentage of points earned.

The point value of each assignment is given in that assignment's
column in row 10 (as seen with $s in the col. O and Q formulas).

The first student is listed on row 13, and that student's scores are
typed in the assignment columns in row 13.

Col O = SUMIF(Y13:AH13,">0",Y$10:AH$10)

Col P = (Y13+Z13+AA13+AB13+AC13+AD13+AE13+AF13+AG13+AH13)

Col Q =(((Y13/Y$10)+(Z13/Z$10)+(AA13/AA$10)+(AB13/AB$10)+(AC13/AC$10)+
(AD13/AD$10)+(AE13/AE$10)+(AF13/AF$10)+(AG13/AG$10)+(AH13/AH$10))*100)/
COUNT(Y13,Z13,AA13,AB13,AC13,AD13,AE13,AF13,AG13,AH13)

And, do this for all the students-rows 13, 18, 23, 28-and so-on until
the end?

NOTE-there are 4 rows in between each student's name (the first
student's rows are 13 through 17, the 2nd student's rows are 18
through 22, etc.), and there is different data on each row.
Currently, the last row between each student is blank.

Thanks,
Arnold

 

[Bob Phillips]

Just simplify the formulae

O = SUMIF(Y13:AZ13,">0",Y$10:AZ$10)
P = SUM(Y13:AZ13)
Q = AVERAGE(IF((Y13:AZ13<>0)*(Y$10:AZ$10<>0),Y13:AZ13/Y$10:AZ$10))*100

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Arnold]

Thanks Bob, I didn't mention that there are other columns that start
after the last assignment column. The other columns, which are for
daily attendence and participation points and progress notes currently
start in column AI (leaving one blank column between the last
assignment column and the first date column). I suppose I could do as
you suggested and insert blank columns to be used for assignments.
However, manual manipulation would still have to be done--extra blank
columns would have to be removed later, or additional ones added if
needed.

 

[Bob Phillips]

Arnold,

Keep column AH blank and change the formulae to absolute columns. Then, when
you need a new column, insert BEFORE AH, and everything will be maintained.

O = SUMIF($Y13:AH13,">0",$Y$10:AH$10)
P = SUM($Y13:Ah13)
Q = AVERAGE(IF(($Y13:AH13<>0)*($Y$10:AH$10<>0),$Y13:AH13/$Y$10:AH$10))*100



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Arnold]

OK, will try. Thanks