Calculating an average of data between two dates
 

Hello all,
I have attached a spreadsheet I am using the track GCSE practice question grades.

You can enter the raw mark per practice question, the spreadsheet then calculates the percentage score, and assigns a grade from this.

There are three columns per practice question. The maximum potential mark is put in row 2 (next to the "P"), in row 3 the columns are labelled "A", "%", "G" - for Actual Mark, Percentage Score and Grade.

The marks are entered in the "Teacher View" sheet. I want to AVERAGE the percentage marks for a term, based on dates in the 'BoundariesTerm' sheet, and the date in Row 1, Column D, G etc etc. (the date is located in the same column as the percentage).

In the spreadsheet I attached, - for example working on row 5, with the formula in AA5.

Term 1 is between 03/09/12 and 26/10/12, there are three mark entries in this period (09/09/12, 15/09/12, 06/10/12) so the formula would average D5, G5 and J5.

IF we changed the dates to Term 4 (between 25/02/13 and 28/03/13) then it would average S5 only (sole entry in this date range)

This is to be calculated per student (formula replicated on each row).

LINK TO FILE
http://www.excelforum.com/attachment...9&d=1343662958

Luke

Calculating an average of data between two dates
 

Assuming that you want the results in 'Summary View'!F4:L33 and that the student order will always be the same in both sheets, try this in 'Summary View'!F4

=AVERAGEIFS('Teacher View'!$D5:$Z5,'Teacher View'!$D$1:$Z$1,"="&OFFSET(BoundariesTerms!$J$2,C OLUMN()-COLUMN($F:$F),0),'Teacher View'!$D$1:$Z$1,"<="&OFFSET(BoundariesTerms!$K$2,C OLUMN()-COLUMN($F:$F),0))

Copied across and downward.