Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update References in Formulas when Columns are Inserted and Rows are Skipped
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. If a new assignment is added-next would be in col. AH-that is worth x# of points, is there a way for Excel to change the formulas to the following: 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,A H13) 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update References in Formulas when Columns are Inserted and Rows are Skipped
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" wrote in message oups.com... 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. If a new assignment is added-next would be in col. AH-that is worth x# of points, is there a way for Excel to change the formulas to the following: 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,A H13) 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update References in Formulas when Columns are Inserted and Rows are Skipped
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update References in Formulas when Columns are Inserted and Rows are Skipped
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:AH 13/$Y$10:AH$10))*100 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Arnold" wrote in message ups.com... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update References in Formulas when Columns are Inserted and Rows are Skipped
OK, will try. Thanks
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I get linked data to update inserted rows | Excel Discussion (Misc queries) | |||
Automatically copy formulas/functions to inserted rows. | Excel Worksheet Functions | |||
how do you get a formula to not update when rows are inserted | Excel Discussion (Misc queries) | |||
Formula doesn't update when rows are inserted | Excel Worksheet Functions | |||
Can array formulas work if rows are inserted? | Excel Worksheet Functions |