MrExcel Publishing
Your One Stop for Excel Tips & Solutions


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:


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


if you want to comply exactly with the SUMPRODUCT syntax.