What is the AVERAGEIF function in Excel?
→ The MS Excel AVERAGEIF function is used for getting average (arithmetic mean) of numbers based on given criteria. This function makes an average of numbers of the defined range if that meets defined criteria.
→ We can use logical operators like (>,<,<>,=) and wildcards (*,?) for defining the criteria.
→ The criteria may be the exact match or it may be the partial match. In both conditions, this function will help us.
→ AVERAGEIF function can be applied to the numeric value and this function also returns a numeric value.
→ We can put the AVERAGEIF function in the Statistical Function Category
How to use the AVERAGEIF function in Excel?
→ You can refer to the below mentioned detailed Microsoft Excel tutorial to understand the AVERAGEIF Function.
→ We can simply use the AVERAGEIF function in MS Excel to get the average of cells based on numbers that meet specific conditions.
What is the syntax of the AVERAGEIF function?
→ The syntax for the AVERAGEIF function in Microsoft Excel is:
= AVERAGEIF(range,criteria,[average_range])
The Syntax Parameter or Syntax Arguments are:
→ range - The range is the range of cells from that we need to decide and apply the criteria.
→ criteria - The criteria are used to determine which cells to be added during the average.
→ average_range – The average range is the range of cells form that we need to make average if the specific condition will be met. In other words, if the criteria will fulfill then the numbers will be averaged from the average range.
Examples of AVERAGEIF Function:
→ For a better understanding of the AVERAGEIF function, we can take one example.
→ In the below picture, we can see the different cars sold in different years.
→ So I need to make an average for a specific model which model sold how many quantity.
→ For this, we will use the AVERAGEIF function.
Example: We need to calculate average qty. of BMW sold in all year
→ So, from the syntax, we know that the AVERAGEIF function has three parameters/arguments that We will understand one by one.
Parameter 1: Range
→ The first parameter in the AVERAGEIF function is the “range”. So we need to decide from which range we need to compare the criteria.
→ In our case, we need to check the average qty. sold of BMW car. So our criteria is “BMW”. We need to choose the range in which the BMW is falling in this example it is B2:B10. So that is our range.
→ So our range = B2:B10 in this example.
Parameter 2: Criteria
→ In simple words from the selected range of for which product/category we want to make average is called the criteria.
→ In the above example, we need average BMW car sold from selected range so our criteria is “BMW”. (Note: If we are mentioning the criteria as a text then we need to add text in between the double invited comma “”. E.g. “BMW”.)
→ We can mention criteria with two different methods. (1) we can mention as “BMW” or (2) We can give reference of cell-like in the above example we can give the cell reference = B2 cell because the value of B2 cell is BMW.
→ So our criteria = "BMW" or B2 cell in this example.
Parameter 3: Average_Range
→ The third parameter of the AVERAGEIF function is the range of numbers that will be averaged.
→ In other words, in our example, we need to calculate the average qty. sold of BMW car.
→ The sold qty. is available in cell range C2:C10. We need to make an average of this range so this is our average range. From this range, the average will be made.
→ So our average_range = C2:C10 in this example.
→ So as mentioned in the above example we have applied the formula =AVERAGEIF(B2:B10,"BMW",C2:C10).
→ This formula finds the car name "BMW" from the range B2:B20 and make an average from average_range C2:C10.
→ So we can get the result as avarage BMW car sold per year = 333.33
👉 For a regular update:
➨ Join us (Telegram Channel)
➨ Join us (LinkedIn Page)
👉 Also Navigate Our Popular Category:
➨ Lookup & Reference Functions
Post a Comment