how to specify the first cell with data in it inside an if statement

[cs.bonak]

I keep a running avg of kids grades in Excel over a 2 week period. Th
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 wa
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 showe
up D so i changed i

 

[Ron Rosenfeld]

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 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)-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.