Average Function in Excel: Using AVERAGE in Microsoft Excel

Finding the average value of a result, a data poll or a range of data that you are given can be very difficult, especially if you are dealing with large, unwieldy numbers like those in standard form or with many decimal places. Much like using a calculator, Microsoft Excel is a powerful tool to use if you need to wrangle numbers and data sets. Often, you don’t want to have to go out of the program you are working in just to do some mathematical operations on a calculator. Luckily, Microsoft Excel is a very capable program that is able to cope with almost any type of mathematical formulae that you throw at in. Microsoft Excel is a great program that makes good looking charts, tables and spreadsheets.

To try to get a handle on Excel, you could try this course based in Microsoft Excel 2010, which does a great job at showing you the functionality of the program in a way that is easily understood and show you how to implement what you learn in the course to your daily workload.

Learning what your tools are capable of is always worth spending a few hours on and the course covers a large amount of use cases that Excel is useful in.

Averages with a Formula

An average in mathematics is defined as the result of adding multiple amounts together and then dividing this by the number of separate amounts. Using averages within Excel is easy, assuming you understand the syntax for the command and you are aware of how to enter formulas into cells in Excel.

To use a formula in Excel, make sure that the formula bar is able to be written in by going in to the VIEW button on the toolbar in Microsoft Excel. After you go in to VIEW, there should be an option to display the formula bar. Sometimes, this option is not enabled by default and you will have to turn it on before entering formulae. When you have enabled the formula bar, enter in this formula.

=AVERAGE (argument1, argument2,)

Adding the = sign is very important as this tells Excel to read this cell as a formula rather than an integer or other type of cell data. The AVERAGE command takes the average of all of the arguments and variables that you put inside the pair of brackets. In the brackets, you can have up to 255 separate values or arguments. In general, with the AVERAGE command, you will be using arguments or variables that are cell numbers in your document. This can be shown by making the formula look like this.

=AVERAGE(E16:E27)

This would make Microsoft Excel add up all the contents of the cells E16 through to E27, then divide them by the amount of cells that contain data. This is a useful command when you want to make averages appear at the bottom of a column, say if you were tracking sports statistics for baseball or football like batting averages or average pass lengths by a player.

If you are struggling with these types of formulas I recommend that you take a look at this course in Excel Functions which will educate you in many of the most used functions within Microsoft’s Excel. This will help make sense of syntax, which is very important throughout Microsoft’s Excel package.

Average Mode with Autosum

Whilst you can use this AVERAGE function in a hard coded formula, there is an easier way to do this. The autosum button, denoted by a large Z looking character in the Office toolbar has an average mode.

To quickly average the contents of a series of cells, simply select all the cells you wish to average, then click on the auto sum button or right click on your data. There should be a section of the contextual menu devoted to the AutoSum functionality and within the sub menu you will find the Average button. This will take the work out from creating a formula with each of the cells you wish to average together by doing it automatically. It is interesting to note that the auto average functionality of Microsoft’s Excel program adds in exactly the same formula as the one in this document. The auto average functionality of Excel just saves a little time. Microsoft includes many of these little time savers and calls them Macros. They are little scripts that are written in Visual Basic and can be used to save you time and automate repetitive and simple tasks that have defined parameters to them. If you often need to average things for example, you can create macros to do so, or use the AutoSum macro that exists already to do this.

Why Use Averages

Averages are a very useful tool for anyone who is collecting a large amount of data and has a large amount of different categories of data, as averages can be used to show broad patterns and hint at continuity within multiple streams of data.

Averaging is extremely important in many mathematical fields like Chemistry and Physics as well as anything that relies on quantitative information such as Sociology and Psychology. The data that is gathered from these fields is very dependant on methodology. If you work in these fields and are having trouble finding great data to work with, take a look at this course in The Zen Of Data Gathering to see how you can speed up your productivity within Microsoft’s Excel program.

Whilst averaging is a common task with a pre-built macro, you can create your own macros to do your work faster. Macro writing is a form of programming so if you are not confident with programming, taking a course in programming basics like this PHP for Beginners course will help you get to grips with the correct syntax for macros and improve the quality of your work.