I am looking for a formula that calculates :
- the average of the last x entries in a row - that are NOT formulas, and
that are greater than 0 -
and
- where the header of that row is equal to "xyz".
Thanks,
Gilbert
Since one of your criteria is the absence of a formula, you must use VBA. Enter
this UDF in the same manner as I suggested in your previous thread, and use the
=AvgLastX(...) in a similar manner.
See the comments in the UDF for HELP on what the different arguments mean.
===================================
Option Explicit
Function AvgLastX(DataTbl As Range, _
lRowNum As Long, _
x As Long, _
hdr As String) As Double
'Assumes "hdr" is in first row of DataTbl
'lRowNum as the row number within the table to be averaged
'x is the number of values to be averaged
Dim i As Long
Dim dSumVals As Double
Dim lCount As Long
'DataTbl should have at least as many rows as lRowNum
If DataTbl.Rows.Count < lRowNum Then
AvgLastX = CVErr(xlErrRef)
Exit Function
End If
For i = DataTbl.Columns.Count To 1 Step -1
If DataTbl(1, i).Value = hdr And _
DataTbl(lRowNum, i).HasFormula = False Then
dSumVals = dSumVals + DataTbl(lRowNum, i).Value
lCount = lCount + 1
If lCount = x Then Exit For
End If
Next i
AvgLastX = dSumVals / lCount
End Function
========================================
--ron
