Reputation: 291
I would like to find a formula that enables me to calculate at once what the following two formulas calculate individually. The formula would have to ignore blank cells.
1) A formula that enables me to average the nth last entries in a column and that ignores blank cells (entered using CTRL+SHIFT+ENTER)
=AVERAGE(IF(ROW(A1:A10)>=LARGE(IF(A1:A10,ROW(A1:A10)),nth),IF(A1:A10,A1:A10)))
2) A formula that averages values if a certain condition is met in an adjacent column (entered using CTRL+SHIFT+ENTER)
=AVERAGE(IF(A1:A10="CONDITION",B1:B10))
Of course, I would have to manually input the "nth value" as well as the condition.
This example demonstrates what I would like to accomplish (see text below)
What would the average be for the last two entries where the condition is "Blue"? (7.5)
What would the average be for the last three entries where the condition is "Red"? (7)
etc.
Again please note the blank cells.
Upvotes: 0
Views: 969
Reputation: 46451
You can use this array formula to average the last 3 non-blank Blues
=AVERAGE(IF(A1:A10="Blue",IF(ISNUMBER(B1:B10),IF(ROW(B1:B10)>=LARGE(IF(A1:A10 ="Blue",IF(ISNUMBER(B1:B10),ROW(B1:B10))),3),B1:B10))))
Change condition and n value if required.
what result do you expect if there aren't 3 non-blank Blues? with the above you'll get a #NUM! error
Upvotes: 1
Reputation: 96791
First enter the following UDF() (User Defined Function) in a standard module:
Public Function averaje(s As String, nItems As Long, r1 As Range, r2 As Range) As Variant
Dim N As Long, Kount As Long, i As Long
Dim n1 As Long, n2 As Long, c1 As Long, c2 As Long
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
n1 = 0
n2 = 0
c1 = r1.Column
c2 = r2.Column
N = Cells(Rows.Count, c1).End(xlUp).Row
Kount = 0
For i = N To 1 Step -1
If Cells(i, c1).Value = s Then
If n1 = 0 Then n1 = i
Kount = Kount + 1
n2 = i
If Kount = nItems Then GoTo DONE
End If
Next i
averaje = "Not enough items"
Exit Function
DONE:
averaje = wf.Average(Range(Cells(n1, c2), Cells(n2, c2)))
End Function
User Defined Functions (UDFs) are very easy to install and use:
If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx
To remove the UDF:
To use the UDF from Excel:
=averaje("Red",3,A:A,B:B)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
and for specifics on UDFs, see:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
Macros must be enabled for this to work!
For example:
Upvotes: 0