Excel AVERAGE Function

AVERAGE Function (Table of Contents)

• AVERAGE in Excel
• AVERAGE Formula in Excel
• How To Use AVERAGE Function in Excel?

AVERAGE in Excel

• AVERAGE function: It’s an in-built function in excel that is categorized under Statistical functions. It is used as worksheet & VBA function in excel

• According to the mathematical definition, Arithmetic mean or Average is calculated by adding numbers together, then dividing the total by how many numbers are being averaged.
• Its most commonly used function in a financial & statistical domain, where it will be helpful to analyze yearly average sales & revenue for a company
• It is simple & Easy to use Excel function with few arguments

Definition

AVERAGE: Calculate the arithmetic mean or average of a given set of arguments

AVERAGE Formula in Excel

Below is the AVERAGE Formula:

The Formula or Syntax for an Average function is:

Where

Number1: Numeric values for which we want to find the average, first argument is compulsory & it is required. subsequent arguments are optional i.e. (from Number 2 onwards)

Arguments can be given as numbers or cell references, or ranges

Up to 255 numbers can be added

How to use AVERAGE Function in Excel?

AVERAGE Function is very simple to use. Let us now see how to use the AVERAGE function in Excel with the help of some examples.

AVERAGE in Excel – Example #1

AVERAGE Calculation for a set of Numbers

With the help of Average function, I need to find out an average of given numbers in the cell

Let’s apply Average function in cell “C12”. Select the cell “C12” where an Average function needs to be applied

Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “AVERAGE” in the search for a function box, an AVERAGE function will appear in select a function box. Double click on an AVERAGE function.

A dialog box appears where arguments for average function needs to be filled or entered i.e. =Average (Number1, [Number 2]…) You can enter the numbers directly into the number arguments which are present in cells C8 TO C11.

Click ok, after entering all the numbers.

i.e. =AVERAGE(8,5,7,4) returns 6 as the result.

AVERAGE in Excel – Example #2

To Calculate AVERAGE for Rows & Columns data

A) Average function for column range

In the below-mentioned example, Table contains subject-wise marks or score of a student in column C (C19 TO C24 is the column range)

With the help of Average function, I need to find out average score of a student in that column range (C19 TO C24 is the column range). Let’s apply Average function in cell “C25”. Select the cell “C25” where an Average function needs to be applied.

Click the insert function button (fx) under formula toolbar, dialog box will appear, Type the keyword “AVERAGE” in the search for a function box, an AVERAGE function will appear in select a function box. Double click on an AVERAGE function

A dialog box appears where arguments for AVERAGE function needs to be filled or entered i.e. =Average (Number1, [Number 2]…).

Here, we need to enter the column range in Number1 argument, click inside cell C19 and you’ll see the cell selected, then Select the cells till C24. So that column range will get selected i.e. C19:C24. Click ok, after entering the column range.

i.e. =AVERAGE(C19:C24) returns 48.33333333 as the result.  The average subject score is 48.33333333

B) Average function for row range

In the below-mentioned example, Table contains subject-wise marks or score of a student in a row number 19 i.e. score or number row (F19 TO K19 is the row range)

With the help of Average function, I need to find out average score of a student in that row range (F19 TO K19). Let’s apply Average function in cell “L19”.Select the cell “L19”. where an Average function needs to be applied.

Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “AVERAGE” in the search for a function box, an AVERAGE function will appear in select a function box. Double click on an AVERAGE function.

A dialog box appears where arguments for AVERAGE function needs to be filled or entered i.e. =Average (Number1, [Number 2]…).

Here, we need to enter Number1 argument, So, click inside cell F19 and you’ll see the cell selected, then Select the cells till K19. So that row range will get selected i.e. F19:K19. Click ok, after entering the row range.

i.e. =AVERAGE(F19:K19) returns 71 as the result.  The average subject score is 71

AVERAGE in Excel – Example #3

To Calculate AVERAGE Time

Average function helps out to calculate the average time that includes hours, minutes and seconds for a marathon race

In the below-mentioned table, it contains a time data of top three racers, i.e. Time taken by them to finish the race. (in hours, minutes and seconds format)

With the help of average function, I need to find out average time. Let’s apply Average function in cell “I10”. Select the cell “I10”. where the Average function needs to be applied.

Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “AVERAGE” in the search for a function box, an AVERAGE function will appear in select a function box. Double click on AVERAGE function.

A dialog box appears where arguments for AVERAGE function needs to be filled or entered i.e. =Average (Number1, [Number 2]…).

Here, we need to enter Number1 argument, So, click inside cell I7 and you’ll see the cell selected, then Select the cells till I9. So that column range will get selected i.e. I7:I9. Click ok, after entering the column range.

i.e. =AVERAGE(I7:I9) returns 1:44:21 as the result.  The average time value is 1:44:21

Things to Remember

• Cells containing Boolean operators, text strings, TRUE, FALSE, and empty or blank cells will be ignored, (Note: it will not ignore the cells with the value zero)
• If any of the supplied argument is entered directly, if it cannot be interpreted as numeric values, it gives #VALUE! error G. = AVERAGE (“Two”, “Five”, “Six”)
• If any of the supplied argument is arguments are non-numeric, it gives #DIV/0! Error
• AVERAGE function differs from AVERAGE A function, where an AVERAGE function is used to find an average of cells with any data i.e. text values, numbers and Boolean
• AVERAGE function is not restricted to find the average of a table range or row range or column range or non-adjacent cells. You can specify individual cells in number arguments to get the desired results.

Recommended Articles

This has been a guide to Excel AVERAGE function. Here we discuss the AVERAGE Formula and how to use an AVERAGE function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Use of Excel RAND Function
2. Examples of Scrollbar in Excel
3. Guide to Excel NPV Function
4. How to Use MID Function?