Use Excel's MEDIAN Function to Find the Middle Value GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office
How to Use the MEDIAN Function in Excel
Find the middle value in a group of supplied numbers
By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on April 24, 2020 Tweet Share Email Tweet Share Email In This Article
Expand Jump to a Section How the MEDIAN Function Works MEDIAN Function Syntax Step-by-Step: Enter the MEDIAN function Blank Cells vs Zero Values Microsoft Excel has several functions that will calculate commonly used average values. The MEDIAN function finds the median or middle value in a list of numbers. These instructions apply to Excel 2019, 2016, 2013, and 2010, as well as Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, Excel for Microsoft 365, and Excel Online. How the MEDIAN Function Works
The MEDIAN function sorts through the provided arguments to find the value that falls arithmetically in the middle of the group. If there's an odd number of arguments, the MEDIAN function identifies the middle value in the range as the median value. If there's an even number of arguments, the function takes the arithmetic mean or the average of the middle two values as the median value. The values supplied as arguments don't have to be in any particular order for the function to work. MEDIAN Function Syntax
A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments. The following is the syntax for the MEDIAN function: =MEDIAN(Number1, Number2, Number3, ...) =MEDIAN is how all MEDIAN formulas begin. Number1 refers to the required data that the function will calculate. Number2 and subsequent values refer to optional additional data values to be calculated in the average. The maximum number of entries allowed is 255, each of which must be separated by a comma. This argument can contain a list of numbers to be averaged, cell references to the location of the data in the worksheet, a range of cell references, and a named range. Type the complete function into a worksheet cell or enter the function and arguments using the Function dialog box. MEDIAN Function Example
These steps detail how to enter the MEDIAN function and arguments using the dialog box. We're using sample data entered into a spreadsheet, as seen below. Select cell G2, which is where the results will display. Select the Insert Function button to open the Insert Function dialog box. Choose Statistical in the Category list. Select MEDIAN in the list of functions and then select OK. Highlight cells A2 to F2 in the worksheet to automatically insert that range. Press Enter or Return to complete the function and return to the worksheet. For our example data, the answer 20 should appear in cell G2. If you click on cell G2, the complete function, =MEDIAN(A2: F2), appears in the formula bar above the worksheet. Why is the median value 20? Since there's an odd number of arguments in this line of data (five), the median value is calculated by finding the middle number. It's 20 here because there are two numbers greater (49 and 65) and two numbers smaller (4 and 12). Blank Cells vs Zero Values
When finding the median in Excel, there's a difference between blank or empty cells and those containing a zero value. The MEDIAN function ignores blank cells but not those containing a zero value. By default, Excel displays a zero in cells with a zero value. This option can be turned off, so if cells are left blank, the zero value for that cell is still included as an argument for the function when calculating the median. Here's how to toggle this option on and off: In Excel 2019 Excel 2016 Excel 2013 and Excel 2010
Go to the File tab and select Options. Go into the Advanced category from the left pane of the options. On the right side, scroll down until you find the Display Options for This Worksheet section. To hide zero values in cells, clear the Show a zero in cells that have zero value checkbox. To display zeros, put a check in the box. Save any changes with the OK button. In Excel 2019 for Mac Excel 2016 for Mac and Excel for Mac 2011
Go to the Excel menu. Select Preferences. Under Authoring, select View. Clear the Show Zero Values checkbox under Window Options. This option can't be turned off in Excel Online. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Use the Google Spreadsheets AVERAGE Function Use the Excel RIGHT Function to Extract Characters How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Combine the ROUND and SUM Functions in Excel Excel SUMIFS: Sum Only Values Meeting Multiple Criteria How to Use Excel's MROUND Function Use Excel's EOMONTH Function to Add or Subtract Months How to Count Data in Selected Cells With Excel's COUNTIF Function Ignore Error Values When Finding the Average in Excel How to Use the Excel TRUNC Function Excel QUOTIENT Function: Divide Numbers Find The MEDIAN IF Your Formula Meets Criteria in Excel How to Use the Round Function in Excel How to Use a Dynamic Range in Excel With COUNTIF and INDIRECT Round up Numbers in Excel With the ROUNDUP Function How to Use the DAY function in Excel Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies