CODEWITHSUNDEEP
excelconditional-statementsaverage
gmorissette
gmorissette

Reputation: 291

Find average for nth last entries matching a certain condition - Excel

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)

enter image description here

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

Answers (2)

barry houdini
barry houdini

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

Gary's Student
Gary's Student

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:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

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:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

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:

enter image description here

Upvotes: 0

Related Questions