Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGE with conditions | Excel Worksheet Functions | |||
Adding up entries in one column with conditions in two columns | Excel Discussion (Misc queries) | |||
average low 10 of last 20 entries | Excel Worksheet Functions | |||
Unique Entries with Conditions | Excel Worksheet Functions | |||
average on 2 conditions | Excel Worksheet Functions |