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)



"Buster" wrote in message
...
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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Buster" schrieb im Newsbeitrag
...
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

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

THX
"Peo Sjoblom" wrote in message
...
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)



"Buster" wrote in message
...
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