Average of the last x entries with conditions
 

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

Average of the last x entries with conditions
 

On Sun, 28 Sep 2008 19:46:21 +0200, "Gilbert DE CEULAER"
wrote:

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


What version of Excel?
--ron

Average of the last x entries with conditions
 

2003 SP3
Gilbert

"Ron Rosenfeld" wrote in message
...
On Sun, 28 Sep 2008 19:46:21 +0200, "Gilbert DE CEULAER"
wrote:

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


What version of Excel?
--ron

Average of the last x entries with conditions
 

On Sun, 28 Sep 2008 19:46:21 +0200, "Gilbert DE CEULAER"
wrote:

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