Average Formula in Excel Definition with Formula:
The average formula in excel calculates the average of given numbers. Average means the sum of numbers divided by the total numbers. Suppose there are 6 numbers, i.e., a,b,c,d,e,f then
Average=(a+b+c+d+e+f)/6
To make the above calculation easy, we use the average formula in excel.
Formula: =AVERAGE(number1, [number2],..)
The above formula ignores all the empty cells or cells that have zeros. But we want to include those empty cells into the calculation and then use
Formula: =AVERAGEA(number1, [number2],..)
Averageif Function in Excel Definition with Formula:
The averageif function in excel calculates the average of the values if the given criteria is satisfied.
Formula: =AVERAGEIF(range, criteria, [average_range])
Suppose we have the following table:
range= E6:E12
criteria= “A”
average_range= F6:F12
Solution Formula: =AVERAGEIF(E6:E12,”A”,F6:F12)
Answer: 25
On matching criteria(“A”) the range(E6:E12), we get four values (23, 11, 44, 22) from the average_range as these values match the criteria. So, the average is [(23+11+44+22)/4] = 25
Averageifs Function in Excel Definition with Formula:
The averageifs function in excel calculates the average of the values if the given criteria are satisfied.
Formula: =AVERAGEIFS(average_range, criteria_range1, criteria1,….)
Suppose we have the following table:
average_range= G6:G12
criteria1= “Red”
criteria_range1= E6:E12
criteria2= “A”
criteria_range2= F6:F12
Solution Formula: =AVERAGEIFS(G6:G12,E6:E12,”Red”,F6:F12, “A”)
Answer: 33.5
On matching criteria1(“Red”) and criteria2(“A”) with the range1 and range2 resp., we get two values (23, 44) as both values match all criteria. So, the average is [(23+44)/2] = 33.5
Explanation with Example Questions:
Brenda is having a rough day as her profit from the cakes that she bakes and sells decreasing for the past few months. Her orders are the same, but still, profit is reducing.
On analysing the excel sheets, she came across the fact that the average cost of the ingredients has increased. She wants to solve this problem, but she has some other tasks to do also.
Read More: How to Divide in Excel?
She calls Carly for help as she knows that Carly learned the basics of average formula in excel, averageif function in excel and averageifs function in excel during her computer classes in school.
Brenda prepares some questions to check Carly’s knowledge of the average formula in excel.
- Calculate the average price of all ingredients.
- Calculate the average price of all bananas.
- Calculate the average price of all bananas purchased from the Mall.
- Calculate Mean in Excel.
- Calculate the weighted average in Excel.
Now coming to the answers:
Calculate the average price of all ingredients
Carly knows how to solve this question. She uses the average formula to solve this.
Formula: =AVERAGE(G6:G13)
Answer: $4.50
Calculate the average price of all bananas
Carly solves this question by using the averageif function in excel.
Formula: =AVERAGEIF(E6:E13,”Banana”,G6:G13)
Answer: $5.00
When the criterion(Banana) matches the criteria_range(E6:E13), we get the average of matched items from the G6:G13 range.
As per the table, we get banana prices in three different rows. So, the average is (3+5+7)/3 = 5.
Calculate the average price of all bananas purchased from the Mall
Carly solves this question using the averageifs function in excel.
Formula: =AVERAGEIFS(G6:G13,E6:E13,”Banana”,F6:F13,”Mall”)
Answer: $6
When criterion1(Banana) matches the criteria_range1(E6:E13) and criterion2(Mall) matches the criteria_range2(F6:F13), we get the average of matched items from the G6:G13 range.
As per the table, we get banana prices in two rows purchased from the Mall. So, the average is (5+7)/2 = 6.
Calculate Mean in Excel
Mean means the average. So mean function in excel is the same as the average formula in excel.
To calculate mean use:
=AVERAGE(number1, [number2],..)
=AVERAGEA(number1, [number2],..)
=AVERAGEIF(range, criteria, [average_range]
=AVERAGEIFS(average_range, criteria_range1, criteria1,….)
Calculate the weighted average in Excel
While calculating the average in excel, we assume that all the values have the same weightage. But in real life, different items have different weightage.
For example:
In the school, internal exams, practicals, and external exams all have different weightages when calculating the final result.
Keeping this in mind, Carly calculates the weighted average in excel using the weighted average formula.
The weighted average formula includes the Sumproduct formula and the Sum formula.
Formula: =SUMPRODUCT(G6:G13,H6:H13)/SUM(H6:H13)
Answer: $4.85
This formula calculates as (3+5+5+3+5+5+5+5+5+4+4+7+7)/13 =63/13=4.85
Carly solves all these questions of average formula, and Brenda agrees that she’ll be able to help her in her business.