Find last used cell and return cell address formula

[tims31]

I have a sheet which has data in a column but the length of the column range can change so I need a formula that will find the cell address of the last used cell in that column and be able to insert it so it can be used in the AVERAGE forumla =AVERAGE(E13:E[last cell used address]) The columns starts at row 13 and has only numeric values. How would this be inserted into the average formula?

Is this possible in a formula and not using VBA please?

 

[Peter_SSs]

Could you just choose a number of rows greater than you are ever likely to use and have a formula like this?

=AVERAGE(E13:E20000)

 

[tims31]

Try:
=AVERAGE(E13:INDEX(E:E,match(1E100,E:E)))

Chris, Sorry but yours still returns a zero value but thanks Rory yours has done the trick. If I want to change this for other columns do I just need to change the RED E's and not the '1E100' . Can you please explain what the 1E100 does?
=AVERAGE(E13:INDEX(E:E,match(1E100,E:E)))

 

[tims31]

Could you just choose a number of rows greater than you are ever likely to use and have a formula like this?

=AVERAGE(E13:E20000)

Yes that works too, perhaps I was just looking for an elegant solution and over complicating it.

Thanks

 

[RoryA]

Chris, Sorry but yours still returns a zero value but thanks Rory yours has done the trick. If I want to change this for other columns do I just need to change the RED E's and not the '1E100' . Can you please explain what the 1E100 does?
=AVERAGE(E13:INDEX(E:E,match(1E100,E:E)))

Correct. 1E100 is just a very big number - larger than anything that will be in your column, so MATCH will end up just finding the last number value.

 

[tims31]

Correct. 1E100 is just a very big number - larger than anything that will be in your column, so MATCH will end up just finding the last number value.

Excelent, thanks very much for all your help.