| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting the correct reference cell to work
I am using Excel 2000.
I have created a exam score tracking sheet and I want to be able to display the most recent exam average once the new scores have been entered. The only thing I can come up with is changing my formula to reflect the proper cell after it populates. I am trying to figure out a way to have Excel do this automatically. If anyone has any ideas I would greatly appreciate them. Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getting the correct reference cell to work
Place the scores in a column, say column Z. The average will always be:
=AVERAGE(Z:Z) This will adjust as values are added. -- Gary''s Student - gsnu200789 "Derek" wrote: I am using Excel 2000. I have created a exam score tracking sheet and I want to be able to display the most recent exam average once the new scores have been entered. The only thing I can come up with is changing my formula to reflect the proper cell after it populates. I am trying to figure out a way to have Excel do this automatically. If anyone has any ideas I would greatly appreciate them. Thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getting the correct reference cell to work
The class will have a total of 7 exams. There are 3 sections to the class
and all 3 are taking the class together, but are tracked seperately. I am trying to figure out a way that when the section average populates into its cell to take that information with the other 2 sections and get the average of those 3. Below is what I am talking about: Lets say I have a list for EO Class. Below this are the 7 exam averages for this class. Below this there is the RO Class list and them the SRO Class list. Now once each exam average score is computed, I am then trying to get the average of those 3 for the most recent exam to populate in its own cell. Right now the formula I have to do this is as follows: =IF(COUNT(AC7,AC20,AC33)0,SUM(AC7,AC20,AC33)/COUNT(AC7,AC20,AC33),"") When the next exam score gets entered, then its scores will be in AC8, AC21, and AC34. How would I get Excel to automatically give me the latest test score average? "Gary''s Student" wrote: Place the scores in a column, say column Z. The average will always be: =AVERAGE(Z:Z) This will adjust as values are added. -- Gary''s Student - gsnu200789 "Derek" wrote: I am using Excel 2000. I have created a exam score tracking sheet and I want to be able to display the most recent exam average once the new scores have been entered. The only thing I can come up with is changing my formula to reflect the proper cell after it populates. I am trying to figure out a way to have Excel do this automatically. If anyone has any ideas I would greatly appreciate them. Thanks. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getting the correct reference cell to work
Let's use a help column, say AD. In AD1 thru AD39 enter:
=IF(AC7="","",AC7) =IF(AC20="","",AC20) =IF(AC33="","",AC33) =IF(AC8="","",AC8) =IF(AC21="","",AC21) =IF(AC34="","",AC34) =IF(AC9="","",AC9) =IF(AC22="","",AC22) =IF(AC35="","",AC35) =IF(AC10="","",AC10) =IF(AC23="","",AC23) =IF(AC36="","",AC36) =IF(AC11="","",AC11) =IF(AC24="","",AC24) =IF(AC37="","",AC37) =IF(AC12="","",AC12) =IF(AC25="","",AC25) =IF(AC38="","",AC38) =IF(AC13="","",AC13) =IF(AC26="","",AC26) =IF(AC39="","",AC39) =IF(AC14="","",AC14) =IF(AC27="","",AC27) =IF(AC40="","",AC40) =IF(AC15="","",AC15) =IF(AC28="","",AC28) =IF(AC41="","",AC41) =IF(AC16="","",AC16) =IF(AC29="","",AC29) =IF(AC42="","",AC42) =IF(AC17="","",AC17) =IF(AC30="","",AC30) =IF(AC43="","",AC43) =IF(AC18="","",AC18) =IF(AC31="","",AC31) =IF(AC44="","",AC44) =IF(AC19="","",AC19) =IF(AC32="","",AC32) =IF(AC45="","",AC45) Now column AD has contiguous triplets to be averaged. Then all we need as the average of the last three items in column AD: =AVERAGE(OFFSET(AD1,MATCH(2,1/(AD1:AD39<0))-1,,-3)) This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200789 "Derek" wrote: The class will have a total of 7 exams. There are 3 sections to the class and all 3 are taking the class together, but are tracked seperately. I am trying to figure out a way that when the section average populates into its cell to take that information with the other 2 sections and get the average of those 3. Below is what I am talking about: Lets say I have a list for EO Class. Below this are the 7 exam averages for this class. Below this there is the RO Class list and them the SRO Class list. Now once each exam average score is computed, I am then trying to get the average of those 3 for the most recent exam to populate in its own cell. Right now the formula I have to do this is as follows: =IF(COUNT(AC7,AC20,AC33)0,SUM(AC7,AC20,AC33)/COUNT(AC7,AC20,AC33),"") When the next exam score gets entered, then its scores will be in AC8, AC21, and AC34. How would I get Excel to automatically give me the latest test score average? "Gary''s Student" wrote: Place the scores in a column, say column Z. The average will always be: =AVERAGE(Z:Z) This will adjust as values are added. -- Gary''s Student - gsnu200789 "Derek" wrote: I am using Excel 2000. I have created a exam score tracking sheet and I want to be able to display the most recent exam average once the new scores have been entered. The only thing I can come up with is changing my formula to reflect the proper cell after it populates. I am trying to figure out a way to have Excel do this automatically. If anyone has any ideas I would greatly appreciate them. Thanks. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getting the correct reference cell to work
I entered what you recommended and I get a #DIV/0 error.
"Gary''s Student" wrote: Let's use a help column, say AD. In AD1 thru AD39 enter: =IF(AC7="","",AC7) =IF(AC20="","",AC20) =IF(AC33="","",AC33) =IF(AC8="","",AC8) =IF(AC21="","",AC21) =IF(AC34="","",AC34) =IF(AC9="","",AC9) =IF(AC22="","",AC22) =IF(AC35="","",AC35) =IF(AC10="","",AC10) =IF(AC23="","",AC23) =IF(AC36="","",AC36) =IF(AC11="","",AC11) =IF(AC24="","",AC24) =IF(AC37="","",AC37) =IF(AC12="","",AC12) =IF(AC25="","",AC25) =IF(AC38="","",AC38) =IF(AC13="","",AC13) =IF(AC26="","",AC26) =IF(AC39="","",AC39) =IF(AC14="","",AC14) =IF(AC27="","",AC27) =IF(AC40="","",AC40) =IF(AC15="","",AC15) =IF(AC28="","",AC28) =IF(AC41="","",AC41) =IF(AC16="","",AC16) =IF(AC29="","",AC29) =IF(AC42="","",AC42) =IF(AC17="","",AC17) =IF(AC30="","",AC30) =IF(AC43="","",AC43) =IF(AC18="","",AC18) =IF(AC31="","",AC31) =IF(AC44="","",AC44) =IF(AC19="","",AC19) =IF(AC32="","",AC32) =IF(AC45="","",AC45) Now column AD has contiguous triplets to be averaged. Then all we need as the average of the last three items in column AD: =AVERAGE(OFFSET(AD1,MATCH(2,1/(AD1:AD39<0))-1,,-3)) This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200789 "Derek" wrote: The class will have a total of 7 exams. There are 3 sections to the class and all 3 are taking the class together, but are tracked seperately. I am trying to figure out a way that when the section average populates into its cell to take that information with the other 2 sections and get the average of those 3. Below is what I am talking about: Lets say I have a list for EO Class. Below this are the 7 exam averages for this class. Below this there is the RO Class list and them the SRO Class list. Now once each exam average score is computed, I am then trying to get the average of those 3 for the most recent exam to populate in its own cell. Right now the formula I have to do this is as follows: =IF(COUNT(AC7,AC20,AC33)0,SUM(AC7,AC20,AC33)/COUNT(AC7,AC20,AC33),"") When the next exam score gets entered, then its scores will be in AC8, AC21, and AC34. How would I get Excel to automatically give me the latest test score average? "Gary''s Student" wrote: Place the scores in a column, say column Z. The average will always be: =AVERAGE(Z:Z) This will adjust as values are added. -- Gary''s Student - gsnu200789 "Derek" wrote: I am using Excel 2000. I have created a exam score tracking sheet and I want to be able to display the most recent exam average once the new scores have been entered. The only thing I can come up with is changing my formula to reflect the proper cell after it populates. I am trying to figure out a way to have Excel do this automatically. If anyone has any ideas I would greatly appreciate them. Thanks. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getting the correct reference cell to work
Try this formula instead:
=AVERAGE(OFFSET(INDIRECT("AD"&COUNT(AD:AD)),,,-3)) This is a "normal" formula, not an array formula -- Gary''s Student - gsnu200789 "Derek" wrote: I entered what you recommended and I get a #DIV/0 error. "Gary''s Student" wrote: Let's use a help column, say AD. In AD1 thru AD39 enter: =IF(AC7="","",AC7) =IF(AC20="","",AC20) =IF(AC33="","",AC33) =IF(AC8="","",AC8) =IF(AC21="","",AC21) =IF(AC34="","",AC34) =IF(AC9="","",AC9) =IF(AC22="","",AC22) =IF(AC35="","",AC35) =IF(AC10="","",AC10) =IF(AC23="","",AC23) =IF(AC36="","",AC36) =IF(AC11="","",AC11) =IF(AC24="","",AC24) =IF(AC37="","",AC37) =IF(AC12="","",AC12) =IF(AC25="","",AC25) =IF(AC38="","",AC38) =IF(AC13="","",AC13) =IF(AC26="","",AC26) =IF(AC39="","",AC39) =IF(AC14="","",AC14) =IF(AC27="","",AC27) =IF(AC40="","",AC40) =IF(AC15="","",AC15) =IF(AC28="","",AC28) =IF(AC41="","",AC41) =IF(AC16="","",AC16) =IF(AC29="","",AC29) =IF(AC42="","",AC42) =IF(AC17="","",AC17) =IF(AC30="","",AC30) =IF(AC43="","",AC43) =IF(AC18="","",AC18) =IF(AC31="","",AC31) =IF(AC44="","",AC44) =IF(AC19="","",AC19) =IF(AC32="","",AC32) =IF(AC45="","",AC45) Now column AD has contiguous triplets to be averaged. Then all we need as the average of the last three items in column AD: =AVERAGE(OFFSET(AD1,MATCH(2,1/(AD1:AD39<0))-1,,-3)) This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200789 "Derek" wrote: The class will have a total of 7 exams. There are 3 sections to the class and all 3 are taking the class together, but are tracked seperately. I am trying to figure out a way that when the section average populates into its cell to take that information with the other 2 sections and get the average of those 3. Below is what I am talking about: Lets say I have a list for EO Class. Below this are the 7 exam averages for this class. Below this there is the RO Class list and them the SRO Class list. Now once each exam average score is computed, I am then trying to get the average of those 3 for the most recent exam to populate in its own cell. Right now the formula I have to do this is as follows: =IF(COUNT(AC7,AC20,AC33)0,SUM(AC7,AC20,AC33)/COUNT(AC7,AC20,AC33),"") When the next exam score gets entered, then its scores will be in AC8, AC21, and AC34. How would I get Excel to automatically give me the latest test score average? "Gary''s Student" wrote: Place the scores in a column, say column Z. The average will always be: =AVERAGE(Z:Z) This will adjust as values are added. -- Gary''s Student - gsnu200789 "Derek" wrote: I am using Excel 2000. I have created a exam score tracking sheet and I want to be able to display the most recent exam average once the new scores have been entered. The only thing I can come up with is changing my formula to reflect the proper cell after it populates. I am trying to figure out a way to have Excel do this automatically. If anyone has any ideas I would greatly appreciate them. Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Filling a formula down-need correct cell reference | Excel Worksheet Functions | |||
| COUNTIF cell reference won't work | Excel Worksheet Functions | |||
| IF Function Does Not Work With Cell Reference | Excel Worksheet Functions | |||
| Copy formula down a column does not use correct cell reference | Excel Discussion (Misc queries) | |||
| copied formula has correct cell reference, but result of original | Excel Worksheet Functions | |||
Linear Mode
