Last cell In calcualtion problem

[Buster]

I'm trying to average a range of numbers in a calculation as follows;
=AVERAGE('Earned Value'!H2,(ADDRESS(MATCH(E1+30,'Earned
Value'!H:H),COLUMN('Earned Value'!H2))))

I know the starting cell but not the last cell as the column data changes
daily.

Any help is appreciated

Jeff

 

[Peo Sjoblom]

If H2 is the first cell and you don't have any empty cells in-between you
can use

=AVERAGE(H2:INDEX(H:H,COUNT(H:H)+1))

+1 is to offset that the numbers don't start in row 1, so if the numbers
start in H3 use

=AVERAGE(H3:INDEX(H:H,COUNT(H:H)+2))

it can also be written

=AVERAGE(OFFSET(H2,,,COUNT(H:H)))

but the latter formula is volatile and re-calculates whenever the sheet is
re-calculated

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Frank Kabel]

Hi
why not make the range large enough?. Try:
=AVERAGE('Earned Value'!H2:H10000)
for example.

 

[Buster]

Thanks Peo, I simply added my sheet name references and it worked like a
charm, and allwithout VBA!

THX