Last cell In calcualtion problem

  • Thread starter Thread starter Buster
  • Start date Start date
B

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
 
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)
 
Hi
why not make the range large enough?. Try:
=AVERAGE('Earned Value'!H2:H10000)
for example.
 
Thanks Peo, I simply added my sheet name references and it worked like a
charm, and allwithout VBA!

THX
 
Back
Top