Calculating an average of data between two dates

[lukestkd]

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

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

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

The marks are entered in the "Teacher View" sheet. I want to AVERAGE th
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 locate
in the same column as the percentage).

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

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

IF we changed the dates to Term 4 (between 25/02/13 and 28/03/13) the
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://tinyurl.com/d63xzkl

Luk

 

[Stunn]

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,COLUMN()-COLUMN($F:$F),0),'Teacher View'!$D$1:$Z$1,"<="&OFFSET(BoundariesTerms!$K$2,COLUMN()-COLUMN($F:$F),0))

Copied across and downward.