Fabulous Examples of Excel Functions:
AVERAGE, MEDIAN, MODE, MAX, and MIN

Microsoft Excel provides many built-in functions. This tutorial covers the Excel functions of AVERAGE, MEDIAN, MODE, MAX, and MIN. We also define these terms, and provide examples and worksheet images to help explain these functions.

Author: Patricia Lynn

If you like video-based introduction, check out Excel 2010 Tutorial for Beginners. We've reviewed this course (over 9 hours of hands-on lessons) and found it very well done.

Let's begin by defining Average, Median, and Mode, and seeing how they differ. Our examples below use the number set: 2 2 3 4

Click on a link below to go directly to the function listed:

• AVERAGE
• MEDIAN
• MODE
• MAX
• MIN

AVERAGE

When you find the average of a group of numbers, you calculate their sum and divide the sum by the number of numbers in the group. To have Microsoft Excel find an average, use the AVERAGE function. The syntax of the AVERAGE function is:

AVERAGE(number1,number2,number3, ... )

… where number1, number2, etc. are from 1 to 255 arguments, for which you want the average. The arguments can reference cells that contain numbers, formulas, or functions.

Cells that contain the AVERAGE function should be formatted to a desired number of decimal points as the decimal portion may be infinite. If a cell is not formatted, Excel displays 2 decimal places by default; which may not be what you want.

• =AVERAGE(8,9,4)  This Excel function adds the three numbers listed (8 + 9 + 4 = 21) and divides the sum by 3 (21 ÷ 3 = 7). Excel returns a value of 7
• =AVERAGE(2,3,5,7)  This Excel function adds the four numbers listed (2 + 3 + 5 + 7 = 17) and divides the sum by 4 (17 ÷ 4 = 4.25. Excel returns a value of 4.25
• =AVERAGE(4,5,2)  This Excel function adds the three numbers listed (4 + 5 + 2 = 11) and divides the sum by 3 (11 ÷ 3 = 3.66666..... As mentioned above, when calculating averages, format the cell appropriately. If this cell was formatted to display 3 decimal places, Excel would display 3.667 in the cell

The arguments of the AVERAGE function can contain formulas and functions. And the AVERAGE function can be embedded in other functions. For example:

• =AVERAGE(PRODUCT(A2,A3),A4)  This Excel function first calculates the product of the contents of A2 and A3. Then it adds this product to the contents of cell A4, divides the result by 2, and displays the average. For more information about the PRODUCT function, see our tutorial Microsoft Excel: The PRODUCT Function.
• =SUM(AVERAGE(A2:A4),A6)  This Excel function first finds the average of the contents of cells A2, A3, and A4. It then adds the result to the contents of cell A6 and displays the sum

MEDIAN

The MEDIAN function calculates the median of a group of numbers. The median is the middle number in an odd group of numbers, or the average of the two middle numbers in an even group of numbers. Our examples below will make this clear. The syntax of the MEDIAN function is:

MEDIAN(number1,number2, ... )

… where number1, number2, etc. are from 1 to 255 arguments, for which you want the median. The arguments can reference cells that contain numbers, formulas, or functions.

Let's discuss the four examples in the Excel spreadsheet above:

• Example 1 =MEDIAN(A1:A5):  We are asking Excel to find the middle number in this set of 5 numbers. Lined up in order we have 2, 3, 5, 7, 8. Excel will return a value of 5
• Example 2 =MEDIAN(B1:B5):  Lined up in order we have 0, 0, 2, 3, 4. There is an odd number of cells, so Excel returns a value of 2
• Example 3 =MEDIAN(C1:C5):  Excel ignores empty cells, so we have an even set of numbers, and Excel will average the middle 2 numbers. Lined up in order we have 2, 3, 6, 8. Excel averages 3 and 6 and returns a value of 4.50. Since the calculation of a median can result in a number containing a decimal portion, consider formatting the cell
• Example 4 =MEDIAN(D1:D4):  Again we have an even set of numbers. Lined up in order the numbers read 0, 2, 2, 9. Excel will average of the two middle numbers, 2 and 2, and return a value of 2

We can also find the median of cell contents which are not contiguous. For example, Excel would return a value of 3 for this function: =MEDIAN(C1:C4,A1). Lined up in order we have 2, 2, 3, 6, 8, and the middle number is 3.

Likewise, Excel would also calculate the median of =MEDIAN(A3,B3,B4,D3) as 3. The values from these 4 cells. lined up in order, are: 0, 2, 4, 5. Excel averages 2 and 4 to return a value 3.

MODE

The MODE function calculates and displays the most frequently occurring number in a group of numbers. The syntax of the MODE function is:

MODE(number1,number2, ... )

… where number1, number2, etc. are from 1 to 255 arguments, for which you want to find the mode.

Excel ignores text, logical values (e.g. True, False), and empty cells when calculating mode. If there is no repeated number in a data set, Excel returns the #N/A error. If there are multiple numbers that repeat the same number of times, Excel displays the highest number. (I.e. if the data set contains two 4s and two 8s, Excel returns a value of 8).

Let's discuss the four examples in the Excel spreadsheet above.

• Example 1 =MODE(B1:B5):  We are asking Excel to find the most repeated number in this set of 5 numbers. The only number that repeats is 0 (zero), so Excel returns the value of 0 for the mode
• Example 2 =MODE(A1:A5,7):  There is no number that repeats in cells A1 through A5, but we also included the number 7 in our arguments. As such, the number set now contains two 7s, so Excel returns a value of 7
• Example 3 =MODE(A1:D5):  This sample function contains one argument, which specifies an array of 4 rows and 5 columns, beginning in the top left corner at A1 and ending in the bottom right corner at D5. The most repeated number in this set is 2
• Example 1 =MODE(C1:C4):  No number repeats in cells C1 through C4, so Excel displays the #N/A error value in the cell

We can also find the mode of cell contents which are not contiguous. In the function =MODE(A1,B4,C2,D3), Excel would return a value of 2.

MAX

The MAX function determines the largest value from the range specified in the arguments.. The syntax of the MAX function is:

MAX(number1,number2, ... )

…where number1, number2, etc. are from 1 to 255 arguments, which can contain cell references, formulas, or functions that resolve to numeric values.

Look at the first two examples in the worksheet below:

• Example 1:  This Excel function determines the largest value among the numbers in cells A1 through A5, and the number 500. Excel returns a value of 755 (cell A3)
• Example 2:  This Excel function determines the largest value among the numbers in cells A1 through A5, and the number 900. Excel returns a value of 900

MIN

The Excel MIN function is identical to the MAX function, except Excel determines the smallest value from the range specified in the arguments. Review the last two examples from the worksheet above:

• Example 1:  This Excel function determines the smallest value among the array spanning two columns from cell A1 to cell B5. Excel returns a value of 20 (cell A4)
• Example 2:  This Excel function determines the smallest value among the numbers in cells B1 through B5, and the number 100. Excel returns a value of 65 (cell B4)

Consider locking cells containing functions and formulas, and protecting worksheets, to prevent accidental erasure. Instructions for locking cells and protecting worksheets are provided in the tutorial Excel Made Easy: A Beginner's Guide.

Don't forget that all Excel functions start with an equal = sign unless they are imbedded inside other functions. We hope this article has been helpful. Cheers!

   Back to Top