Mean of a MOVING Range

[CPerry]

To find the mean of cells A1:A20 you do =AVERAGE(A1:A20) but my problem is I don't know which one is my first cell (A1) and which is my last (A20) because the information moves. It will always be in column A but I need a formula that looks for the first value, the last value and takes an average of them and everything in between please.

I've found on the internet already, to find the first non empty cell you do... =INDEX(A1:A20,MATCH(TRUE,INDEX((A1:A20<>0),0),0)) but this only gives the number not the cell reference.

To find the last occupied cell reference you do... ="A" & LOOKUP(2,1/(A:A<>""),ROW(A:A))

I've tried modifying the first formula and then putting both inside an =Average() formula but no luck so far. How do I do this please?

[yujin]

Just devide the sum by the count.

=SUM(A1:A20)/COUNTIF(A1:A20,"<>0")

[Paul_Hossler]

Can you use a user defined function?

Option Explicit

'Usage - =MyAverage(A:A)

'   need to use whole column to force recalc


Function MyAverage(r As Range) As Variant
    Dim r1 As Range, r2 As Range
    
    On Error GoTo ErrorExit
    
    Application.Volatile
    
    Set r1 = r.Cells(1, 1).EntireColumn.Cells(1, 1).End(xlDown)
    Set r2 = r.Cells(1, 1).EntireColumn.Cells(r.Parent.Rows.Count, 1).End(xlUp).Offset(1, 0)
    
    MyAverage = Application.WorksheetFunction.Average(Range(r1, r2))
    
    
    Exit Function
ErrorExit:
    MyAverage = CVErr(xlErrNum)
End Function

[CPerry]

I can't yujin because the actual system uses far more than 20 cells down like in the example and it's not just the mean I'd like to use this range for. I'd like to explore the median, interquartile range, standard deviation etc so it's the (First Filled Cell:Last Filled Cell) part of the formula that is the important part.

@ Paul_Hossier same as the above commend mate. Is there a way of just having a formula for now please? I'll use VBA to speed up the process once I know which factors are most important but I just need a RANGE formula so I can do: =Average(RANGE), =STDEV(RANGE), =MEDIAN(RANGE) etc

[offthelip]

I am making a bit of an assumption here , but if this requirement is to do with your betangel project, then wouldn't it be easier to keep a record of where the start and the end of the range is when you are writing the data into the range?
It almost undoubtedly will be faster.

[CPerry]

Yep, my lovely little project. I don't know what calculations will need to be made with this range as of yet though. Skewness, kurtosis, mean, stdev etc I have about a dozen I'd like to analyse in greater detail once I've collected a good amount of past inputs. Thought there would be a simple formula somewhere on the internet but I haven't found it yet. Thought it would be a semi-common thing people would search for, obviously not Guess most people keep their work simple and know where the beginning AND the end of their ranges are lol not me...

[offthelip]

I don't know what calculations will need to be made with this range as of yet though. Skewness, kurtosis, mean, stdev etc

All the more reason to calculate the extent of the range once and use it for all possible calculations. Since the betangel data always arrives in the same cells and you then need to process and copy it somewhere else , e.g as per the price/ decay routine I have been helping you with, it is much easier to keep a check on the first and last row out of the 351 that you actually write into. This would be a simple modification to the "Add amount" routine.