Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating hourly average from large data set | Excel Discussion (Misc queries) | |||
How do I ignore "#N/A" data in a column while calculating average | Excel Worksheet Functions | |||
Calculating Average | Excel Discussion (Misc queries) | |||
calculating average on bottom 50% of data | Excel Discussion (Misc queries) | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) |