If you want to find the average of numbers in a column based on asingle condition,you can use the AVERAGEIF, AVERAGE & IF functions to retrieve the output.
AVERAGEIF: Finds average (arithmetic mean) for the cells specified by a given condition or criteria.
range: Therange ofcells from which you want the criteria to be evaluated.
criteria: The criteria define which cells to average.
average_range: The range of cells that you want 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 the condition is met then it returns the preset value, and if the condition isnot met 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 & their corresponding numbers in column B.There is a criteria in cell D2.We need a formula that will retrieve the output in cell E2 based on the condition set in cell D2.
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 incorrect.
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.