Excel Average Function: Average() Versus AverageA() Formulas
The Excel Average Function is absolutely necessary, when you need to make calculations or lookup values that are buried in your data or simply compare values. So learning which ones to use and how to use them is imperative if you want to bump up your skill level.
Excel Average Function
SYNTAX of the Excel Average Function:
- =Average(1st Number, [Optional 2nd Number and so on ...])
- Only includes cells with valid numbers in them and divides by that number
- Average(2, 6, 17, 11) = 9
- Average(B2:E2) = Average of numbers in the range B2:E2
Excel AverageA Function
SYNTAX of the Excel AverageA Function:
- =AverageA(1st Value, [Optional 2nd Value and so on ...])
- Calculates ALL cells in the range, including non-numeric values.
- Calculates cells with text as zero and counts the cell when calculating the average.
- Calculates logic as follows: True = 1; False = 0 and counts the cell when calculating the average.
In the example above, you can see the Values column has Numbers, Logical Statements as well as some text. AverageA() takes all 8 cells and adds them up before dividing by 8. The Average() formula would only count the values in the 5 cells that have numbers in them and divide by 5, giving a very different answer.
20 + 60 + 110 + 30 + 230 = 450 / 5
20 + 0 + 60 + 110 + 1 + 0 + 30 + 230 = 451 / 8
So, why would you use AverageA() over Average()?
Most often, you would get by just fine using the Average() formula, but there are times where AverageA() would serve you better. Lets say you are the accountant for a church or some other membership type of organization. Hopefully, the person that accumulated the list for you put $0.00 for those that haven't paid yet, but sometimes they will put some text, like None, Nothing, Not Yet, etc... In that case, AverageA() will include those members and automatically calculate their payments as $0.00 and add them to the total number of members. This allows you to include all Members even when there are non-numerical entries in your data.
Whether you use the Average() formula, or the AverageA() formula really depends on your needs at the time, so it is important to know what data you need to include in your calculations, before using either of them. At this point, you should be able to explain the difference to those that can answer the question about which one to use, based on how they want the values calculated.
Share, Comment, Ask a Question or Rip Me a New One!
If you liked this article, please feel free to share it on social media and comment below if you have any suggestions, questions or corrections. Thank you for reading!
P.S. - Remember to sign up to the newsletter email list, so you can catch all of the new posts and learn about upcoming topic, products and/or services.