If you want to find the average of numbers in a column based on multiple conditions,you can use the “AVERAGEIFS”, “AVERAGE” & “IF” functions to retrieve the output in Microsoft Excel 2010.
AVERAGEIFS: Finds average (arithmetic mean) for the cells specified by a given set of conditions or criteria.
average_range: The range of cells for that you want to derive an average.
criteria_range: Therange ofcells from which you want criteria to evaluate.
criteria: The criteria defines which cells to average.
AVERAGE function can be used to find the average value or arithmetic mean of values in a selected range of cells.
number1, number2,……number n are numeric values they can be numbers or names, arrays, of references that contain numbers.
IF:-This function checks if a condition you specify is TRUE or FALSE. If the condition returns TRUE then it returns preset value, and if the condition returns FALSE then it returns another preset value.
Syntax = IF(logical_test,value_if_true,value_if_false)
logical_test: Logical test will test the condition or criteria.If condition meets then it returns the preset value, and if the condition does not meet then it returnsanother preset value.
value_if_true: The value that you want to be returned if this argument returns TRUE.
value_if_false: The value that you want to be returned if this argument returns FALSE.
Let us take an example:
We have some Codes in column A & B & their corresponding numbers in column C.There are two criteria in cells E2 & F2 from column A & column B respectively.We need a formula that will retrieve the output in cell G2 based on the conditions set in cells E2 & F2.
We can achieve the same output by applying the AVERAGE & IF functions together.
Note:- If we will not use CTRL + SHIFT + ENTER then the result would be wrong.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.