MrExcel Publishing
Your One Stop for Excel Tips & Solutions

=AVERAGE(IF(AND))


Posted by Eric on January 24, 2002 7:19 PM

Does anyone know how to find the AVERAGE of Column C, IF the value of Column A is 1, AND the value of Column B is 4.

Please help...


Posted by Aladin Akyurek on January 24, 2002 9:42 PM

You seem to be asking for a multiconditional average, that is, average the cells of C if the corresponding cells in A are equal to 1 and those in B are equal to 4.

If this understanding is correct, add the following UDF to your workbook.
[ To do so, activate Tools|Macro|Visual Basic Editor, then Insert|Module, paste the code in the window with title ... (Code), and leave the editor by activating File|Close and Return to Microsoft Excel. ]

Function Used(r As Range) As Range
'
' Harlan Grove
' Sun 25 Nov 01
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
End Function

The formula to use for averaging column C is:

=SUMPRODUCT((Used(A:A)=E1)*(Used(B:B)=E2),(Used(C:C)))/MAX(1,SUMPRODUCT((Used(A:A)=E1)*(Used(B:B)=E2)))

where E1 houses 1, the condition value that must holds for column A and E2 4, the condition value for column B.

Note. This formula can also be written up as

=SUMPRODUCT((Used(A:A)=E1)+0,(Used(B:B)=E2)+0,(Used(C:C)))/MAX(1,SUMPRODUCT((Used(A:A)=E1)+0,(Used(B:B)=E4)+0))

if you want to comply exactly with the SUMPRODUCT syntax.