Excel Functions Tutorials

- Excel Formulas Cheatsheet
- Excel
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Concatenate Excel Function
- RIGHT Function in Excel
- LEN in Excel
- LEFT Function in Excel
- Search Function in Excel
- TEXT Function in Excel
- PROPER in Excel
- MID in Excel
- Trim in Excel
- CLEAN Excel Function
- EXACT Excel Function
- REPT Function in Excel
- CODE Excel Function
- FIND Function in Excel
- VALUE Function in Excel
- Char Function In Excel
- Substitute Function in Excel
- Substring in Excel

- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- TRANSPOSE Excel Function
- Row Function in Excel
- OFFSET Excel Function
- VLOOKUP with Multiple Criteria
- IFERROR with VLOOKUP in Excel
- Fixing VLOOKUP Errors
- Alternatives to Vlookup

- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- ROUNDDOWN Excel Function
- ROUNDUP Function in Excel
- DSUM
- COMBIN Excel Function
- INT Excel Function (Integer)
- TANH in Excel

- Date and Time Function in Excel
- Statistical Function in Excel
- AVERAGE Excel Function
- CORREL Excel Function
- COUNT Excel Function
- COUNTIF Excel Function
- FREQUENCY Excel Function
- MAX Excel Function
- MEDIAN Excel Function
- GROWTH Excel Function
- SLOPE Function in Excel
- TREND Function in Excel
- SMALL Function in Excel
- MODE Excel Function
- LARGE Excel Function
- PERCENTILE Excel Function
- LINEST Excel Function
- T-TEST in Excel
- QUARTILE Excel Function
- FORECAST Excel Function
- MIN in Excel
- Standard Deviation in Excel
- COUNTIF with Multiple Criteria
- Mean vs Median
- NORM.S.INV Function in Excel
- NORMDIST in Excel
- Variance vs Standard Deviation

- Information Functions in Excel
- Excel Charts
- Column Chart in excel
- Stacked Column Chart
- Pie Chart in Excel
- Area Chart in Excel
- Stacked Chart in Excel (Column, Bar & 100% Stacked)
- Histogram Excel Chart
- Waterfall Chart in Excel
- Pareto Chart in Excel
- Bubble Chart in Excel
- Gantt Chart in Excel
- Radar Chart in Excel (Spider Chart)
- Clustered Bar Chart in Excel
- Clustered Column Chart in Excel
- Dynamic Chart in Excel

- Excel Tools
- Watch Window in Excel
- List Box in Excel VBA
- Conditional Formatting in Excel
- Data Table in Excel
- Data Validation Excel
- Paste Special in Excel (With Top 10 Shortcuts)
- One Variable Data Table in Excel
- Two-Variable Data Table in Excel
- Scenario Manager in Excel
- Solver in Excel
- Name Range in Excel
- Dynamic Named Range in Excel
- Sort Data in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Auto Format Excel
- Track Changes in Excel
- Text to Columns in Excel
- Merge Cells in Excel
- Lock Cells in Excel
- Scroll Bars in Excel
- Spell Check in Excel
- Filters in Excel
- Auto Filter In Excel
- Advanced Filter in Excel
- Recording Macros in Excel
- Add-Ins in excel
- Excel Fill Handle
- AutoFill in Excel
- Excel Tables
- Radio Button in Excel
- Combo Box in Excel and VBA
- Checkbox in Excel

- Excel Tips
- How to Unhide Columns in Excel?
- Convert Date to Text in Excel
- Top 20 Keyboard Shortcuts in Excel
- Convert Numbers to Text in Excel
- Show Formula in Excel
- Absolute Reference in Excel
- Relative References in Excel
- Superscript in Excel
- Subscript in Excel
- Formula Errors in Excel
- Convert Text to Numbers in Excel
- Insert Multiple Rows in Excel
- Remove Blank Rows in Excel
- Highlight Every Other Row in Excel
- External Links in Excel
- Insert Hyperlinks in Excel
- Remove Hyperlinks in Excel
- Combine Cells in Excel
- Remove Duplicates in Excel
- Random Numbers in Excel
- Drop Down List in Excel
- Refresh Pivot Table in Excel
- Column Sort in Excel
- Hiding a Column in Excel
- Count Unique Values in Excel
- CAGR Formula in Excel
- Equations in Excel
- How to Delete Pivot Table?
- Excel Subtraction Formula
- Excel Extensions
- Hide Formula in Excel
- Numbering in Excel
- Auto Numbering in Excel
- Row Limit in Excel
- Delete Row Shortcut in Excel
- Arrays in Excel VBA
- Array Formulas in Excel
- Dynamic Tables in Excel
- Convert Columns to Rows in Excel
- Formatting in Excel
- Print Comments in Excel
- Excel vs Google Sheets

**Excel AVERAGE Function (Table of Contents)**

- AVERAGE Function in Excel
- AVERAGE in Excel Formula
- AVERAGE Function in Excel – Illustration
- How to Use AVERAGE Function in Excel?

## AVERAGE Function in Excel

The AVERAGE function in Excel gives the arithmetic mean of the supplied set of numeric values. It is categorized as a *Statistical Function*.

### AVERAGE in Excel Formula

Below is the AVERAGE in Excel Formula.

**Required:** A number (of the range of numbers) for which the average is to be calculated.

**number 1 **

**Optional:** Additional numbers (of the range of numbers) for which the average is to be calculated.

**[number 2], [number 3], .. [number n] **

These numbers can be given as input as– numbers, named ranges, ranges or cell references that contain numeric values. The input can also be a result of other Excel operations outputting a number in the end. The AVERAGE in excel formula can handle a maximum of 255 individual arguments.

**Returns: **It returns the average of the supplied range of numbers. The cell references containing logical values, text or are empty are ignored by the AVERAGE in excel formula. However, the logical values or text representations of numbers entered directly are counted. If any of the supplied argument entered directly cannot be interpreted as numeric values, it gives #VALUE! error. If all the supplied set of arguments are non-numeric, it gives #DIV/0! Error. The arguments with error values also give an error.

### AVERAGE Function in Excel – Illustration

Suppose you want to find the average of {2, 3, 5, 4, 6}. These numbers are also given in cell B3:B7.

You can enter :

= AVERAGE ( B3: B7 )

It will return the mean i.e., 4 in this case.

You may also enter the numbers directly as:

= AVERAGE (2, 3, 5, 4, 6)

It will also return 4.

However, if you give the input as text as shown below:

= AVERAGE (“Two”, “Three”, “Five”, “Four”, “Six”)

It will give #VALUE! error.

If the input argument are cell references and none of them is a numeric value, as shown below:

=AVERAGE (A3:A7)

It will give #DIV/0! Error.

However, AVERAGE in excel formula accepts the numbers in quotes as shown below:

=AVERAGE ( “2”, “3”, “5”, “4”, “6” )

It will return 4.

**How to Use AVERAGE Function in Excel?**

The AVERAGE function in Excel is a statistical function and is one of the most used functions in Excel. In the financial sector, it is mostly used to calculate the average sales and the average revenue for a specific period of time.

Let us look at some of the examples of the AVERAGE function in Excel.

### AVERAGE in Excel Formula Example #1

Suppose you have subject-wise marks of each student in a batch as shown below.

Now, you want to calculate the average marks of each student. To do so, you can use the AVERAGE formula for excel given below:

= AVERAGE (D4:H4)

for the first student and press Enter.

It will give the average marks obtained by the student Ashwin. Now, drag it to get the average marks of each of the student.

### AVERAGE in Excel Formula Example #2

Suppose you have the monthly sales data of your company. The data is divided into four different zones.

Now, you want

- to calculate the average sales for each month.
- to calculate the average sales for each zone
- to understand in which zone the average sales is highest.

To calculate the average sales for each month, you could use the following AVERAGE Formula for Excel given below:

= AVERAGE (C4:F4)

which will give the average sales for Jan.

Similarly, drag it to get the average sales for rest of the months.

To calculate the average sales for each zone, you can use the AVERAGE Formula for Excel given below:

= AVERAGE (C4:C15)

for the East Zone and so on.

Now, you can also find which of the zone has highest average. To do so, you can simply use the AVERAGE Formula for Excel given below:

= LOOKUP ( MAX(G18:G21), G18:G21, F18:F21)

### AVERAGE in Excel Formula Example #3

Suppose you have the marks for five subjects and you want to calculate the average marks scored by a student in the top four.

To simply calculate the average of the five subjects, you could use the AVERAGE Formula for Excel given below:

= AVERAGE (C4:G4)

However, to calculate the average of the top four marks, you can use the AVERAGE Formula for Excel given below:

= AVERAGE (LARGE (C4:G4, {1, 2, 3, 4} ) )

It will give the average of the top four marks scored by the student of the five subjects and will return 83.

Similarly, drag it to get the average of top four for the rest of the students.

### AVERAGE in Excel Formula Example #4

Suppose you have some data containing both values and text given in a column (Column B here). Now, you want to calculate the average of the last three values of this column.

To find the average of the last 3 digits in the given data, you can use the AVERAGE Formula for Excel given below:

= AVERAGE (LOOKUP (LARGE ( IF ( ISNUMBER (B3:B18), ROW (B3:B18) ), {1,2,3} ), ROW (B3:B18), B3:B18) )

and press CTRL + SHIFT + ENTER or COMMAND + SHIFT + ENTER (for Mac)

**Let us look at the syntax in detail:**

- ISNUMBER (B3:B18) will check if the given input is a number and return logical value TRUE or FALSE. It will return : {TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}
- IF ( ISNUMBER (B3:B18), ROW (B3:B18) ) will filter the numerical values. It will return {3; 4; FALSE; 6; 7; 8; 9; FALSE; 11; 12; FALSE; 14; 15; FALSE; 17; 18}
- LARGE ( IF ( ISNUMBER (B3:B18), ROW (B3:B18) ), {1,2,3} ) will give the three largest number. Here, it will return the last three positions of numerical values in the input. It will return {18; 17; 15}.
- LOOKUP (.., ROW (B3:B18), B3:B18) will then return the corresponding values from {18; 17; 15} from B3:B18 and returns {50000; 90000; 110000}.
- AVERAGE (..) will then give the average of the input.

**Things to Remember About AVERAGE Function in Excel**

- The AVERAGE function in Excel gives the arithmetic mean of the given range of numbers.
- The input can be numbers entered directly, as cell references, or named ranges.
- The numbers are added together and its sum is then divided by the total number of input numbers.
- A maximum of 255 numbers can be supplied.
- The cell references containing logical values, text or are empty are ignored by the AVERAGE function in Excel.
- Cell references containing 0 are counted in the formula.
- If any of the supplied argument entered directly cannot be interpreted as numeric values, it gives #VALUE! error.
- If all the supplied set of arguments are non-numeric, it gives #DIV/0! Error.
- The arguments with error values give an error.

You can download this AVERAGE Function in Excel template here – AVERAGE Function Excel Template

### Recommended Articles

This has been a guide to AVERAGE Function in Excel. Here we discuss the AVERAGE Formula in excel and how to use AVERAGE function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

## Leave a Reply