Microsoft Excel is one of the leading analytical tools available in the market, and is the most used spreadsheet application in the world, being widely used by financial and analytical firms as well as students for calculating and analyzing data. Excel is known for its simple nature and high affectivity. Because of its easy to use and learn implementation, it has become hugely popular all over the world. But without a doubt, the best feature in Excel is its huge array of functions. The AVERAGE function is such a function which is used daily all over the world for finding out the average of a range of numbers that are provided by the Excel consultant or Excel user. We will go over it in this free Excel help article.
There are a few measures of the central tendency to a set of numbers or readings that are very essential for an analysts or accountant. Such measures are usually arithmetic mean, median and mode. The arithmetic mean is used in the AVERAGE function and it calculates the mean of the numbers that are provided by the user to it. So if the user mentions the elements of one or more arrays then, the average of all those array elements together gives the result of the AVERAGE function. It is therefore very easy to use and even easier to compute as it needs only one primary reference value as its argument although it can process up to a length of even 255 arrays of numbers! The function is usually written as AVERAGE (array1, array2…arrayn). Each of these arrays can have a maximum of 20 elements for the function to compute on. But there is a catch though. The array1 is the basic array or cell for which the computation starts and therefore it must be provided in all cases possible while the other arrays are all additional and not at all required for the function to work. These are nonetheless taken into account and are also computed for.
Few examples of the function with some notable instances will make the concept absolutely clear to the user. If someone calls the function as AVERAGE (23, 43) then all it does is take the mean of the two numbers, that is, (23 + 43) / 2 = 33. So, the function gives 33 as the result and displays it to the user for use. Now, suppose the cell B2 has the value 23 and B5 has 43, then calling AVERAGE (B2, B5) will give the same result as 33 to the user. Even then if one decides to go one bit extra and put a 33 after the two cells are added then the function is called as AVERAGE (B2, B5, 33) and this gives the result as: (23 + 43 + 33)/3 = 33, so 33 is obtained as the result after running this function. In this way the function computes the arithmetic mean of the different inputs the user throws at it and gives the result for use.
Here we present a few features and caveats that one might look out for while using the AVERAGE function:
The function can take text, cell representations, array fields, direct numbers etc as its arguments and directly computes on them to give the desired result. However, it cannot make use of any text or symbolic entry that it may encounter while at that particular cell position. So, it will ignore all such entries and move on to the next entry specified by the user.
Cells which have zero values however are included easily into the function when passed as parameters and can be computed on but the cell references which cause error values to be put in the function or text values are often rejected by the function. For including the text representations etc into your calculation it is better if you use AVERAGEA function that the Excel has made for such cases.
The AVERAGE function lacks in the sense that it doesn’t have the provision for putting in any criterion by the user and so genuinely it may pose some problem sometimes with huge manual evaluation required to find out permissible cells. For such cases Excel has already made the AVERAGEIF and the AVERAGEIFS function which can take conditional values into account for computation.
The AVERAGE function is found in all the latest versions of Excel namely Excel 2000, 2003, 2007, 2008, 2010, and 2011.