I keep a running avg of kids grades in Excel over a 2 week period. The way i have the code now

AVERAGE(OFFSET('1'!E4,COUNTA('1'!D;D)-29,):OFFSET('1'!E4,COUNTA('1'!D;D),))

it returns an error if i don't have 2 weeks of data. I found a way around this by doing this

=IFERROR(AVERAGE(OFFSET('1'!E4,COUNTA('1'!D;D)-29,):OFFSET('1'!E4,COUNTA('1'!D;D),)),IFERROR(...-28...-27...
Im Sure there is a better way to do this any help would be appreciated.

I know that its not supposed to be D;D But when i put the : this showed up D:D so i changed it

If you included dates for the grades, you could use an averageif statement

+averageif(A:A, "="& (A15-13), B:B)

where A:A are the dates of the test scores and B:B are the results

A15 would be the last day of the two week period.

Maybe this wont fit in to your template.

On Mon, 14 Jan 2013 02:17:43 +0000, cs.bonak wrote:


I keep a running avg of kids grades in Excel over a 2 week period. The
way i have the code now


AVERAGE(OFFSET('1'!E4,COUNTA('1'!D;D)-29,):OFFSET('1'!E4,COUNTA('1'!D;D),))


it returns an error if i don't have 2 weeks of data. I found a way
around this by doing this


=IFERROR(AVERAGE(OFFSET('1'!E4,COUNTA('1'!D;D)-29,):OFFSET('1'!E4,COUNTA('1'!D;D),)),IFERROR(...-28...-27...
Im Sure there is a better way to do this any help would be appreciated.

I know that its not supposed to be D;D But when i put the : this showed
up D:D so i changed it


I don't understand exactly what you are doing. I don't understand the relationship between your references, the two weeks of grades, and the -29 factor.

But one way is to make the size of reference in OFFSET the desired size, then compute the row offset.

=AVERAGE(OFFSET(E4:E18,COUNTA(D:D)-n,0))

where n might equal 29 would return a range that is 15 cells high, offset from the original E4:E18 range by a specified amount.

If this doesn't help, please provide more detailed information.