Select last 30 cells

[blastpwr1970]

Hi,

Could somebody please help me!

I am trying to select the last 30 cells in a sheet.
Example:
=AVERAGE(B61:B90)
By changing the value of B61:B90 to a variable range that it will only selecting the last 30 cells that have value.

I included an attachment to illustrated better, I hope.

Thank you for all your help in advance
Julio

[Simon Lloyd]

You need to write a series of UDF's (user defined functions) in a standard module, here's teh first one for you:
[VBA]
Function AverageLast30()
Dim Rng As Long, Rng1 As Long
Rng = Range("B" & Rows.Count).End(xlUp).Row - 30
Rng1 = Range("B" & Rows.Count).End(xlUp).Row
AverageLast30 = Application.WorksheetFunction.Average(Range("B" & Rng & ":B" & Rng1))
End Function
[/VBA]you use it like this: instead of =Average(xx:xx) you type =AverageLast30() and it will work just like the normal function, if you need this for the other forumlae just follow the template i have given you!

[blastpwr1970]

The function works, the only thing is that it does not recalculate/update by itself when new values are enter, I have to go to in to the cell and hit enter to update it saving/exit re-start does not work neither F9 (recalculate formula), also if I has less than 30 values it does not seem to like it too much, my values start at B21, it gives me an error of circular formula.
Any sugestions will be appriciated.
Thank you
Julio