MEDIAN Function in Excel
Master the MEDIAN function to find the middle value in datasets. Learn syntax, practical examples, and error solutions for statistical analysis.
Excel
Google SheetsExcelGoogle Sheets=MEDIAN(number1, [number2], ...)Quick Answer
MEDIAN function MEDIAN function is a statistical function in Excel and Google Sheets that returns the middle value in a dataset when numbers are arranged in numerical order. Unlike AVERAGE, MEDIAN is less affected by outliers, making it ideal for analyzing skewed data distributions. The syntax is `=MEDIAN(number1, [number2], ...)` where you can include up to 255 numbers or ranges.
=MEDIAN(number1, [number2], ...)Practical Examples
Basic Salary Analysis
Find the median salary in a small team to understand typical compensation
Real Estate Market Analysis
Determine median home price in a neighborhood
Student Test Score Analysis
Calculate median test score to measure typical student performance
Sales Response Time Tracking
Monitor median customer response time for performance metrics
Multiple Range Combination
Calculate median across separate departments or data ranges
Product Review Ratings Analysis
Find median customer rating to assess typical product satisfaction
Financial Portfolio Risk Assessment
Calculate median daily return to understand typical investment performance
Common Errors and Solutions
MEDIAN returns #NUM! error
This occurs when the function receives no numeric values to process. All cells in the range might contain text, errors, or be empty.
1. Verify your range contains at least one numeric value 2. Check for hidden text characters or spaces in cells that appear numeric 3. Use VALUE() function to convert text numbers: =MEDIAN(VALUE(A1:A10)) 4. Remove any error cells from your range or use array formulas to filter them
Always validate your data source before applying MEDIAN. Use conditional formatting to highlight non-numeric cells, or implement data validation to prevent text entry in numeric columns.
Example:
MEDIAN produces #VALUE! error
The function encounters incompatible data types, such as trying to directly reference text values as arguments rather than ranges, or including cells with error values (#N/A, #REF!, etc.) within the range.
1. Ensure all direct arguments are numbers or ranges, not text strings 2. Check for and resolve any error values in your data range 3. Use IFERROR to wrap cells that might contain errors: =MEDIAN(IFERROR(A1:A10,"")) 4. Clean your data by filtering out error rows before applying MEDIAN
Implement error checking in your source data. Use data validation to prevent error entries, or create helper columns that clean data before median calculation. The AGGREGATE function with option 6 can ignore errors automatically.
Example:
MEDIAN returns unexpected or incorrect value
Numbers stored as text won't be included in the calculation. Excel treats text numbers differently from numeric values. Also, hidden rows or filtered data within the range can cause confusion about which values are being analyzed.
1. Convert text to numbers: Select cells, click warning icon, choose 'Convert to Number' 2. Multiply by 1: Create helper column with =A1*1 to force numeric conversion 3. Use VALUE function: =MEDIAN(VALUE(A1:A10)) for text numbers 4. For filtered data, use AGGREGATE: =AGGREGATE(12, 5, A1:A100) - function 12 is MEDIAN, option 5 ignores hidden rows
Set up your spreadsheet with proper number formatting from the start. Use Data → Text to Columns → Finish to convert imported text numbers. Implement data validation rules that only accept numeric entries. Document whether your median should include or exclude filtered/hidden values.
Example:
MEDIAN returns nothing or appears blank
The cell formatting might be set to hide zero values, or the formula is being overridden by conditional formatting. Less commonly, circular references in your spreadsheet can prevent calculation.
1. Check cell number formatting - ensure it's not set to custom format that hides zeros 2. Review conditional formatting rules that might be hiding the result 3. Press F9 while editing the formula to see the actual calculated value 4. Enable iterative calculations if circular references exist (File → Options → Formulas) 5. Recalculate all formulas: Ctrl+Alt+F9 (Cmd+Option+Fn+F9 on Mac)
Use consistent cell formatting across your analysis. Avoid conditional formatting that completely hides values. Keep a clear calculation flow without circular dependencies. Set calculation mode to 'Automatic' in Excel options.
Example:
MEDIAN shows #REF! error
The range reference is broken, usually because rows or columns containing the referenced data were deleted, or the formula was moved/copied incorrectly breaking the reference structure.
1. Check the formula for invalid range references (you'll see #REF! in the formula itself) 2. Recreate the formula with correct cell references 3. Use named ranges instead of cell references for more stability: Name your range 'SalesData', then use =MEDIAN(SalesData) 4. If data was deleted, use Undo (Ctrl+Z) to restore it, then move/delete more carefully 5. For moved data, use Find & Replace to update broken references
Create named ranges for important data sets that will be referenced in formulas. Use table references (=MEDIAN(Table1[Salary])) which automatically adjust when rows are added/deleted. Before deleting rows or columns, check for formulas that reference them. Use Excel's 'Trace Dependents' feature to identify formulas that rely on specific cells.
Example:
Best Practices and Pro Tips
When to Use MEDIAN vs AVERAGE
Use MEDIAN when your dataset contains outliers or is skewed (salary data, home prices, customer spending). Use AVERAGE when data is normally distributed and every value should be weighted equally (test scores with no outliers, consistent measurements).
Combine with QUARTILE for Complete Analysis
Pair MEDIAN with QUARTILE functions to understand data distribution. Calculate Q1 (25th percentile), MEDIAN (50th percentile), and Q3 (75th percentile) to see the full picture of your dataset's spread and identify potential outliers.
Ignore Errors with AGGREGATE Function
For datasets that might contain errors, use AGGREGATE function with option 12 (MEDIAN) and option 6 (ignore error values). This is more reliable than wrapping every cell in IFERROR and maintains clean formulas.
MEDIAN Ignores Text and Logical Values
MEDIAN automatically excludes text, logical values (TRUE/FALSE), and empty cells from calculation. Only numeric values are processed. This can lead to unexpected results if your range contains text numbers ('100' vs 100).
Performance Optimization for Large Datasets
For datasets exceeding 10,000 rows, calculate MEDIAN on filtered or sampled data first to verify reasonableness. Use Excel tables instead of ranges for better calculation efficiency. Consider using Power Query for extremely large datasets (100,000+ rows) before applying statistical functions.
Document Your Analysis Context
Always note whether you're calculating median on filtered data, whether hidden rows should be included, and the business meaning of the result. Use cell comments or a separate documentation sheet to explain why MEDIAN was chosen over AVERAGE for your specific analysis.
Cross-Platform Consistency
MEDIAN works identically in Excel and Google Sheets with the same syntax and behavior. This makes it reliable for collaborative work across platforms. However, be aware that Sheets handles extremely large datasets (millions of rows) differently than desktop Excel.
Use Named Ranges for Maintainability
Define named ranges for your data (Formulas → Define Name), then use =MEDIAN(SalaryData) instead of =MEDIAN(B2:B100). This makes formulas self-documenting, prevents reference errors when inserting rows, and allows easier updates to the data range.
Need Help with MEDIAN Function in Excel?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
Master the AVERAGE function with practical examples and error solutions. Learn to calculate mean values and analyze data efficiently in Excel and Sheets.
ExcelSheetsThe COUNT function tallies numeric values in a range, ignoring text and empty cells. Essential for data analysis and statistical calculations.
ExcelSheetsMaster the MAX function to find the largest value in your data. Learn syntax, examples, error handling, and best practices for Excel and Google Sheets.
ExcelSheetsMaster the MIN function to find minimum values in Excel and Google Sheets. Learn examples, handle errors, and optimize data analysis with this guide.
ExcelSheets