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?

 

[Chris Mack]

=AVERAGE(INDIRECT("E13:E"&COUNT(E:E)+12))

Hope this helps,

Chris.

 

[tims31]

Hey Chris, Thanks for the prompt reply.

I tried that but all I get is a zero value from the result...any ideas please?

 

[Chris Mack]

Go through the formula with "Evaluate Formula" on the Formulas tab and see if you can see where it's going wrong.

Does column E contain only the values you want to average? No headings or anything?

 

[AlanY]

how about

=AVERAGE(INDIRECT("E13:E"&COUNT(E:E))</pre>

 

[tims31]

Go through the formula with "Evaluate Formula" on the Formulas tab and see if you can see where it's going wrong.

Does column E contain only the values you want to average? No headings or anything?

Chris,

The column has the results above in rows 2 to 8 and row 12 has a header, the numerical values are all below starting from row 13. I used the evaluate formula but dont really understand the output from this. It says that a function in the formula causes the results to change every time the sheet is calulated

=AVERAGE(INDIRECT("E13:E"&COUNT(E:E)+12))

If I continue to evaluate I get the average of E13:E42 which when evaluating gives zero but if I do this separatly in a different cell just as average E13:E42 I get the result. Also E42 is not the cell with the last content in E36 is?

 

[tims31]

how about

=AVERAGE(INDIRECT("E13:E"&COUNT(E:E))

Hi Alan,

That also returns a zero value for the average. If I can post an image of the sheet I will try and show you that.

 

[tims31]

Please see the image below which shows the layout. I need the formula to go in cell E3 which as you can see shows zero as the value, hope that explains it better

 

[Chris Mack]

=AVERAGE(INDIRECT("E13:E"&COUNTA(E:E)-7+12))

The -7 takes the other 7 non-empty cells out of the count.

Hope this helps,

Chris.

 

[RoryA]

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