Excel offers several useful functions to calculate averages in Excel models. We will briefly discuss the statistical functions available to ensure accurate Excel models.
The statistical functions are useful for the location of the mean, median as well as mode for a specific data set. Their main purpose is to identify the values set upon which the calculation will then be based. Averages for example, can be calculated through a process whereby the sum of values with a set is divided by number of values of present.
If you are a lecturer you will appreciate the ability to find averages in Excel models. It is a simple process whereby Excel adds up the values of a range as selected by the user. This is then divided by the number of cells in that selected range. Not only can the average for a group exam be established, but it can be applied to any situation where you require the average including that of prices, speed, number of respondents, temperatures, and time taken to complete a task etc.
You should however, proceed with caution when it comes to dates since the average for a date range can only be obtained if all the entries in that range are dates. Just a note on the not completely blank cells – if there are blank cells within a range you should proceed with caution as cells with spaces are not seen as empty cells. In general through it should also be noted that the Average function ignores completely empty cells as well as cells that contain text or logical values. Also note that any arguments which contain both text and numbers cannot be calculated in the normal way. For this purpose you should make use of the AVERAGEA function.
Syntax for the use of the average function is:
=AVERAGE(data set range)
A formula will thus look like this:
=AVERAGE(B3:B30)
(Thus calculate the average for cells in the range B3 to B30).
To learn more about the use of statistical functions such as AVERAGE and AVERAGEA in Excel models view our training section.