Brady Gavin
Brady Gavin
Writer
Brady Gavin has been immersed in technology for 15 years and has written over 150 detailed tutorials and explainers. He’s covered everything from Windows 10 registry hacks to Chrome browser tips. Brady has a diploma in Computer Science from Camosun College in Victoria, BC. Read more.
Should you need to find the middle value in a set of data in your spreadsheet without having to do it manually, Google Sheets has a function that does it instantly for you. Here’s how to use the MEDIAN function.
Fire up Google Sheets and open a spreadsheet with datasets for which you want to find the median.
Click on an empty cell and type =MEDIAN(
The MEDIAN function in Google Sheets supports any number of arguments, and anything other than the first value is optional. It will look something like this:
After you press the “Enter” key, the cell will now contain the median of the numbers you put in the function.
If you want to use a range of cells as values, it will look like this:
After you press the “Enter” key, the cell will contain the median value of the range you provided to the function.
Some things to note about the MEDIAN function: Anything entered is sorted numerically, from the lowest to highest value, and cells that are empty—excluding “0”—and that contain text will be ignored by the function.
The MEDIAN function returns the midmost value in a numerical dataset if the set contains an odd number of values. If you enter an even number of values, the MEDIAN function will estimate a response between the two center values.
For example, notice how we enter 36 values, none of which are “$31,” yet the function returns a median value of “$31.” This is because to find the median, the function adds the middle two numbers together and then divides by 2 to estimate the median. So, behind the scenes, it calculates ( 30 + 32) / 2 = 31 to produce the result.
 › How to Rename Columns or Rows in Google Sheets
 › How to Calculate Average in Google Sheets
 › Why SMS Needs to Die
 › How to Restore Taskbar Labels on Windows 11
 › What Does XD Mean, and How Do You Use It?
 › The Worst Thing About Samsung Phones Is Samsung Software
 › 5 Fonts You Should Stop Using (and Better Alternatives)
 › Why Do Clear Phone Cases Turn Yellow?
Brady Gavin
Brady Gavin has been immersed in technology for 15 years and has written over 150 detailed tutorials and explainers. He’s covered everything from Windows 10 registry hacks to Chrome browser tips. Brady has a diploma in Computer Science from Camosun College in Victoria, BC.
Read Full Bio »
value1 – The first value or range to consider when calculating the median value.
value2, . – [ OPTIONAL ] – Additional values or ranges to consider when calculating the median value.
Notes
Although MEDIAN is specified as taking a maximum of 30 arguments, Google Sheets supports an arbitrary number of arguments for this function.
Any text encountered in the value arguments will be ignored.
MEDIAN returns the center value if the dataset contains an odd number of values. If the combined value arguments contain an even number of values, MEDIAN will interpolate between the two center values.
MEDIAN finds the center value of the dataset rather than the mean. To find the mean use AVERAGE or AVERAGEA .
See Also
SMALL : Returns the nth smallest element from a data set, where n is userdefined.
RANK : Returns the rank of a specified value in a dataset.
QUARTILE : Returns a value nearest to a specified quartile of a dataset.
PERCENTRANK : Returns the percentage rank (percentile) of a specified value in a dataset.
PERCENTILE : Returns the value at a given percentile of a dataset.
MINA : Returns the minimum numeric value in a dataset.
MIN : Returns the minimum value in a numeric dataset.
MAXA : Returns the maximum numeric value in a dataset.
MAX : Returns the maximum value in a numeric dataset.
LARGE : Returns the nth largest element from a data set, where n is userdefined.
AVERAGEA : Returns the numerical average value in a dataset.
AVERAGE : The AVERAGE function returns the numerical average value in a dataset, ignoring text.
There are certain statistical metrics that are often used to analyze and get a basic idea about a given set of data. Some of these metrics include the mean, median, mode, and standard deviation.
Among these metrics, the median gives us an idea about the central tendency of the data.
The Google Sheets MEDIAN function helps find the median of a given list of numbers. The function is a versatile one that can be used with a variety of input types.
In this tutorial, we will show you how to find the median in Google Sheets, with different forms of input.
This Article Covers:
What Does the Median Tell Us About a Dataset?
The median of a dataset, like the mean, gives us an idea about the distribution of the data. It tells us at what point the dataset is centered, and also lets us know if the data is skewed.
The median, however, is different from the mean, in that, it calculates the central value of the dataset, unlike the mean.
Moreover, the median requires the list to be sorted, which is not a requirement when finding the mean.
What Does the Google Sheets MEDIAN Function Do?
The MEDIAN function in Google Sheets returns the central value in a numerical list of data.
If the list has an odd number of data items, the function returns the value at the center. If the list contains an even number of data items, then the function returns the arithmetic mean of the central two values.
The median is especially helpful when your dataset contains outliers, because in such cases, the mean does not provide a very good representation of the data.
In fact, if the median of your data is very different from the mean, it indicates the presence of outliers.
How to Find the Median in Google Sheets
To find the median of a dataset in Google Sheets, you can use the MEDIAN function.
The general syntax for this function is as follows:
Here, the arguments can be any of the following four types:
 A set of values
 A range of cell references
 A combination of values and cell references
 A filtered set of values
To understand how to use the MEDIAN function, let us consider an imaginary set of scores for 6 students:
Finding the Median Using a Set of Values as Arguments
One way to find the median is by specifying the direct numeric values in the MEDIAN formula, as follows:
Here, value1, value2,… each correspond to a value in your sample. So in our example, we can type the following formula in the target cell:
Note: You do not need to previously sort the data items, since the MEDIAN function automatically does that for you.
Here’s the result you can expect to get:
Note: If you’re entering numeric values as arguments, you can enter a maximum number of 30 entries only. This, however, does not apply if you use the function with a range of cell references as the argument.
If you get a number with too many decimal places, you can round it off to, say, 2 decimal places by selecting the cell, and navigating to Format>Number>Number from the menu.
Instead of direct values, you could also use cell locations. In our example, you could use the MEDIAN function as follows:
This method, though, is quite inefficient. A better way would be to use the range of locations, as explained below.
Finding the Median Using a Range of Locations as Arguments
The most common way of providing arguments to the MEDIAN function is using a reference to the range of cells containing the data.
 Click on your target cell.
 Type: =MEDIAN(
 Select the cells containing values that you want to calculate the MEDIAN for. You will notice that the range of cell locations that you selected will automatically appear in the cell.
 Close the brackets.
 Press the Return key.
Finding the Median Using a Combination of Values and Location Ranges as Arguments
You can also use a combination of direct values along with cell ranges as inputs to the MEDIAN function.
So you can have something like:
Regardless of the types of arguments you use, you will get the same output for the same data:
Finding the Median Using a Filtered Set of Values as Arguments
In some cases, you might need to apply a filter to your data before calculating the median.
For example, consider the following dataset:
Let’s say you want to find the median for only the students of Section A. You can then wrap the MEDIAN function around a FILTER as follows:
Here, condition is the criteria that qualifies if a cell location is eligible to be considered in the median calculation or not.
So in our example, our formula can be:
Here, the second parameter checks if the cell in the range B2 to B7 is equal to “A”. If so, then it considers the corresponding value of column C in the median calculation.
You can expect to get a result as follows when you apply this formula:
Points to Remember When Using the Google Sheets MEDIAN Function
Here are some important points to remember when using the MEDIAN function in Google Sheets:
 You don’t need to sort the data before applying the MEDIAN function, because it automatically takes care of that.
 The function ignores string values, if any, in your list of arguments.
 The function also ignores empty cells. However, it does not ignore cells containing the value 0.
Other Data Analytic Functions
The MEDIAN function is part of a suite of analytic functions offered by Google Sheets.
Some of the other analytic functions that you can use include:
 The AVERAGE function
 The STDEV function
 The STDEVP and STDEVA functions
In this tutorial, we explained different ways in which you can use the Google Sheets MEDIAN function to estimate the central tendency of a given dataset. We hope this was helpful.
Data analysis 101: learn the ins and outs of the mean, median, and mode calculations in Google Sheets. This is a very important skill for a widelyutilized computer program.
Table of Contents
 How to calculate the mean
 How to calculate the median
 How to calculate the mode
Table of contents
 How to calculate the mean
 How to calculate the median
 How to calculate the mode
You now have a dataset and need to calculate the socalled “measures of central tendency.” How would you do so? Fortunately, Google Sheets has its own functions to automatically calculate the mean, median, and mode of a dataset. They are defined as follows:
 The mean, also known as the arithmetic mean or the average, is calculated by adding all the given values in the list divided by the number of such values.
 The median is the value at the middle of the list after arranging them by increasing order.
 The mode is the most frequent value in the list.
For our example, we have a list of purchases of a single product. We wish to calculate the mean profit, the median of the amount of items ordered, and the mode of the amount of items ordered.
How to calculate the mean
To calculate the mean of a dataset, use the AVERAGE() function and set the input as the area of the array containing the data. For our example, the data for profit covers cells D2 to D56:
=AVERAGE(D2:D56)
The AVERAGE() function ignores text, and so if you accidentally included the header of the table it will not break the function nor change the result:
How to calculate the median
In a similar fashion, the MEDIAN() function calculates the median of the array. For our example, the data for the amount of items ordered covers cells B2 to B56:
=MEDIAN(B2:B56)
The MEDIAN() function ignores text, and so if you accidentally included the header of the table it will not break the function nor change the result:
How to calculate the mode
Finally, the MODE() function calculates the mode of the array. For our example, the data for the amount of items ordered covers cells B2 to B56:
=MODE(B2:B56)
The MODE() function ignores text, so if you accidentally included the header of the table, it won’t break the function nor change the result:
You can use the following formula to perform a Median IF function in Google Sheets:
This formula finds the median value of all cells in some range that belong to a certain group.
When you type this formula into a cell, you need to press Ctrl + Shift + Enter since this is an array formula.
The following example shows how to use this function in practice.
Example: Median IF Function in Google Sheets
Suppose we have the following dataset that shows the total points scored by 15 different basketball players:
Now suppose we’d like to find the median of the points scored by players on each team.
To do so, we can use the =UNIQUE() function to first create a list of the unique teams. We’ll type the following formula into cell F2:
Once we press enter, a list of unique team names will be displayed:
Next, we can use the =MEDIAN() function to find the median number of points scored by players on each team.
We’ll type the following formula into cell G2 and press Ctrl + Shift + Enter so Google Sheets knows this is an array formula:
We’ll then copy and paste this formula into the remaining cells in column G:
Column F displays each of the unique teams and column G displays the median of the points scored by players on each team.
Additional Resources
The following tutorials explain how to calculate other common metrics in Google Sheets:
The MEDIAN function is a premade function in Google Sheets, which returns the middle value in the data.
It is typed =MEDIAN and gets a list of cells:
Note: The median is a type of average value, which describes where the center of the data is located. You can learn about median in our Statistics Median Tutorial.
Calculating the median manually requires that the data is sorted and arranged from low to high. This is not necessary using the MEDIAN function, it does it for you.
Median Function Example
Find the median of this list of numbers 1,1,2,2,3,4,4 :
MEDIAN function, step by step:
 Select a cell H2
 Type =MEDIAN
 Click the MEDIAN command
 Specify the range A2:G2
 Hit enter
Now, the function returns the median:
Great! You successfully found the median using the =MEDIAN function.
Note that it found the value in the middle of the data, which is cell D2 , the one that is marked red:
There are a few ways to use MEDIAN in Google Sheets. The first way is to use the function in the “Math” category. To do this, you would type “=MEDIAN(range)” into a cell, where “range” is the range of cells you want the median of. The second way is to use the “Filter” feature. To do this, you would type “=MEDIAN(range)” into the “Filter” box at the top of a column, and the median of the range will be displayed in the column.
What is the syntax of MEDIAN in Google Sheets?
The MEDIAN function in Google Sheets takes an array of numbers as input and calculates the median of that array. The syntax for the MEDIAN function is as follows:
where “array” is the array of numbers you want to calculate the median of.
What is an example of how to use MEDIAN in Google Sheets?
The MEDIAN function in Google Sheets can be used to find the median value of a given set of numbers. To use the function, simply enter the numbers you want to compare into the function’s parentheses, and then press the Enter key on your keyboard. The median value of the numbers will be automatically calculated and displayed in the cell you selected.
When should you not use MEDIAN in Google Sheets?
There are a few occasions when you should not use MEDIAN in Google Sheets. One such instance is when you are working with data that is not sorted. In this case, the median will not be accurate. Another time you should not use MEDIAN is when you have an extremely small data set. With few data points, the median can be skewed by a few outlier values.
What are some similar formulae to MEDIAN in Google Sheets?
There are a few similar formulae to MEDIAN in Google Sheets that you can use for your data analysis. The first is the AVERAGE formula, which will calculate the average of all the numbers in a given range. The second is the MODE formula, which will return the most common value in a given range. And the third is the QUARTILE formula, which will return the quartile of a given set of data.
There are a few ways to use MODE in Google Sheets. You can use it to find the median of a set of numbers, the most common value in a set of data, or the mode for a grouped set of data. To find the median of a set of numbers, you can use the MODE function in Google Sheets. First, enter the set of numbers you want to find the median for in a single column in Google Sheets. Then, use the MODE function to find the median of that set of numbers. The MODE function will return the median of the set of numbers you entered. To find the most common value in a set of data, you can use the MODE function in Google Sheets. First, enter the set of data you want to find the most common value for in a single column in Google Sheets. Then, use the MODE function to find the most common value in that set of data. The MODE function will return the most common value in the set of data you entered. To find the mode for a grouped set of data, you can use the MODE function in Google Sheets. First, enter the set of data you want to find the mode for in a single column in Google Sheets. Then, use the MODE function to find the mode for that set of data. The MODE function will return the mode for the set of data you entered.
What is the syntax of MODE in Google Sheets?
The MODE function in Google Sheets takes a range of data as an input and finds the most common value in that range. The syntax for the MODE function is: MODE(range)
What is an example of how to use MODE in Google Sheets?
The MODE function in Google Sheets can be used to find the most common value in a data set. To use the function, you first need to enter the data set into a Google Sheet. Then, you can use the MODE function to find the most common value in the data set. The function takes two arguments: the first is the array of data that you want to find the mode for, and the second is the type of mode that you want to find. The function can return three different types of mode: the most common value, the most frequent value, or the mode value.
When should you not use MODE in Google Sheets?
There are a few occasions when you should not use MODE in Google Sheets. One is when you have text data that is not in numerical form. Another is when you have a range of numbers that are not consecutive. In these cases, you should use the AVERAGE function instead of MODE.
What are some similar formulae to MODE in Google Sheets?
The MODE function in Google Sheets calculates the most common value in a given range of data. There are a few similar formulae that you can use to calculate the most common value in a range of data. The MAX function in Google Sheets returns the maximum value in a range of data. The MIN function in Google Sheets returns the minimum value in a range of data. The AVERAGE function in Google Sheets returns the average value in a range of data.
In this tutorial, you will learn how to calculate average on Google Sheets.
There are three main types of average that you may want to calculate when working in Google Sheets: The Mean, Median, and Mode. Fortunately Google Sheets has functions for each of these so all three are easy to calculate.
This tutorial goes through each type of average in turn and describes what it is and the corresponding Google Sheets function.
Calculating Mean in Google Sheets
The most basic type of average is the mean. If someone refers to the average without specifying the type of average, this is what they are most likely referring to. Because of this, the Google Sheets function used to calculate the mean is simply called AVERAGE
The mean of a data set is the sum of all numbers in the dataset, divided by the number of data points.
Here’s how to calculate the mean in Google Sheets:
Step 1
Select the cell you want to calculate the mean in and type the following formula: “=AVERAGE(A:A)”, where the data set you want to find the mean of is stored in column A
Step 2
Hit enter to complete your formula and the calculated mean will appear in the cell
Step 3
By default, the mean will calculate to a large number of digits after the decimal. To reduce the number of digits, select the cell and click on the Decrease Decimal Places button in the toolbar. Keep clicking till you have the right number of digits
Step 4
Note also that you can calculate the mean of multiple ranges but including all of them inside the parentheses separated by commas
Calculating Median in Google Sheets
Another type of average is the median. The median of a data set is the middle number if all numbers are ranked in order, or the mean of the two middle numbers if there are an even number of numbers.
To calculate the median, follow the steps above for calculating the mean, but use the MEDIAN function.
Calculating Mode in Google Sheets
The last commonly used type of average is the mode. The mode of a dataset is the number that appears most frequently in the data set. If more than one number is tied for most frequent, Google Sheets returns the number that appears first in the input arguments
To calculate the mode of a data set, follow the steps for calculating the mean but use the MODE function.
Summary
In this tutorial, I covered how to calculate average on Google Sheets. Want more? Check out all the Google Sheets Tutorials.
When working in Google Sheets there may be times when you want to find the median value of cells that meet certain criteria.
This can be done by combining the MEDIAN function with the IF function to create a Median IF Formula.
There is not a builtin Median IF function in Google Sheets, but by knowing how to use both these functions, we can create one ourselves.
In this tutorial, I will show you how to use a MEDIAN IF formula in Google Sheets.
Table of Contents
Median IF Syntax
To combine the MEDIAN and IF functions in Google Sheets to create a MEDIAN IF formula, this is the syntax:
 criteria_range – this is the range that contains the criteria that you want to check for
 criteria – this is the criteria that you want to use to run the MEDIAN on if the values in your criteria_range are matching this
 median_range – this is the range that contains the numbers that you will use to find the median
Using the Median IF Formula
Now that we know the syntax, I will show you how to use the formula with this example data:
In this example data, I have the sales for various products belonging to different departments. I will use the MEDIAN IF formula to get the median sales values of each department.
Here are the steps:
1. First create 2 new columns where you will create a unique list of the criteria you will use for calculating the median and a column for calculating the median. In this example, my criteria is the department and I am calculating a median for the sales.
2. You can either manually type each unique value, for your criteria or you can use the UNIQUE function on the cell range to return a list of unique values. In this example, my formula is = UNIQUE ( B2:B13 )
3. Now that we have our file set up, we can begin using our MEDIAN IF formula. In this example my formula is = ArrayFormula ( MEDIAN ( IF ( $B$2:$B$13 = E2 , $C$2:$C$13 ) ) ). The first cell range in the formula is the criteria_range ($B$2:$B$13) and I want to find the median if the value in this range equals my criteria (E2). This will then run the MEDIAN function on cell range $C$2:$C$13 for lines that meet my criteria
4. After you have your formula entered into your first cell, you can copy the formula and paste it down your column if you have additional values for which you want to return the median
Closing Thoughts
Learning to create a MEDIAN IF function can seem challenging at first, but once you have tried it a few times, you will find it is actually quite simple.
It helps if you already have an understanding of MEDIAN and IF, so if you are struggling, learn to use those functions first, and make sure to watch the video!
To use the Median IF function in Google Sheet you can use the below formula:
 When you have a list of numbers within a group, you can use the above formula to calculate a Median .
How to use Median IF function in google sheets : Step by Step Guide
In this example, we will go through the steps in detail on How to use Median IF Function in Google Sheets
We will try to calculate the median of the following dataset consisting of football teams with Player’s names and Points table.
We will calculate the median of the points scored by each player from a different team.
Step 1 : Make a list of Unique teams using the =UNIQUE() function
Using the =UNIQUE() function we will create a list of unique teams.
We will write the below formula into cell E2:( Adjust the formula as per your requirement )
Step 2 : Calculate Median points using =MEDIAN() function
Now we will calculate the Median points scored by various players from different teams.
Syntax
 Using the above syntax we will write the below formula into cell E2 .
Press enter,Now we have the median points in the Median Points Table
Step 3 : Now copy the formula in rest of the cells
To copy the formula simply drag the corner of the first cell using the mouse cursor in the Median points column till the end
Now you will have the formula applied in all the remaining cells with a calculated Median .
We hope this tutorial on How to use the median if function in google sheets was helpful
If you need to find the median value of a data set in a spreadsheet without having to manually manipulate it, Google Sheets has the function to immediately do this for you.
If you need to find the median value of a data set in a spreadsheet without having to manually manipulate it, Google Sheets has the function to immediately do this for you. Here’s how to use the MEDIAN function.
Turn on Google Sheets and open the spreadsheet with the data set you want to find the median.
Click an empty cell and type = MEDIAN (, [, .]) into the cell or formula input field, replacing and with values or ranges to consider calculations.
The MEDIAN function in Google Sheets supports any number of arguments and values, in addition to the first value that is optional. The function will look like this:
After you press the Enter key, the cell will now contain the median of the numbers you place in the function.
If you want to use a range of cells as a value, the function will look like this:
After you press the Enter key, the cell will contain the median value of the range you provide for the function.
* Some things to note about MEDIAN function:
Everything entered is sorted by the lowest value to the highest value and blank cells, except for 0, containing the text will be ignored.
MEDIAN returns the median value in a dataset, if the set contains an odd number of values. If you enter an even number of values, MEDIAN will estimate the response between the two central values.
For example, notice when entering 36 values, none of which is ‘$ 31’, but this function still returns the median value of ‘$ 31’ . This is because to find the median, this function adds two middle numbers together and then divides by 2 to estimate the median. Therefore, the function performs the calculation (30 + 32) / 2 = 31 to produce the result.
This article describes the formula syntax and usage of the MEDIAN function in Microsoft Excel.
Description
Returns the median of the given numbers. The median is the number in the middle of a set of numbers.
Syntax
The MEDIAN function syntax has the following arguments:
Number1, number2, . Number1 is required, subsequent numbers are optional. 1 to 255 numbers for which you want the median.
Remarks
If there is an even number of numbers in the set, then MEDIAN calculates the average of the two numbers in the middle. See the second formula in the example.
Arguments can either be numbers or names, arrays, or references that contain numbers.
Logical values and text representations of numbers that you type directly into the list of arguments are counted.
If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
Arguments that are error values or text that cannot be translated into numbers cause errors.
Note: The MEDIAN function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:
Average which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.
Median which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.
Mode which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.
For a symmetrical distribution of a group of numbers, these three measures of central tendency are all the same. For a skewed distribution of a group of numbers, they can be different.
Example
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
The quartile function in Google Sheets makes it simple to calculate quartiles without having to manually reorder your data from smallest to largest.
Quartiles are markers at three points in a dataset. They include:
 The median (middle number)
 The halfway point between the minimum number and median
 The halfway point between the median and the maximum number
The quartile function in Google Sheets also uses the min and max numbers in the dataset.
Therefore, the quartile function uses five quartile numbers to identify each marker including the minimum, maximum, median, and two quartile points.
The numbers used are 0,1, 2, 3, and 4 where 0 returns the minimum value and 4 is the maximum.
Table of Contents
Quartile Function Syntax For Google Sheets
The function will send back the closest value to a specified quartile in your dataset.
The syntax used for this formula is:
 Data – The range or array of data to be considered for the calculation
 Quartile_number – Which quartile number between 0 and 4 to send back
 0 – Gives the minimum value which is the same as using the MIN function
 1 – Gives the value for the first quartile in the dataset
 2 – Gives the value of the median
 3 – Gives the value of the second quartile
 4 – Gives the maximum value which is the same as using the MAX function
An Example of Quartiles in Google Sheets
In the above example, you can see we’ve used the syntax =Quartile(data, quartile_number) in which the data value has been substituted with the data set of A2:A8, and each quartile number from 04 has been represented too.
You just have to put the data set as the first argument and the quartile number as the second. It’s a fairly simple function so long as you remember which number from 0 to 4 represents each quartile marker.
The Complete Breakdown
In the above example, to get the results in the boxes you would have to follow these steps:
 Click the cell you want the result to show in. B2 in the example.
 Type =qu then click on the quartile function
 Click and drag over the desired dataset or manually type in the array.
 Type , on your keyboard
 Enter the desired quartile number from 0 to 4 and press the end bracket key on your keyboard
 Press enter
Another related function to Quartile is the Percentile function. Read more about how to use the percentile function.
Quartile Function FAQs
Is the Quartile Function in Google Sheets Compatible with Microsoft Excel?
Microsoft has replaced the quartile query with quartile.inc and quartile.exc in Excel. The original quartile function still works, but it may be best to use one of the other functions just in case Microsoft makes it incompatible in the future.
If you plan to use your spreadsheet across platforms you will need to keep this in mind. These newer functions are both available in Google Sheets, so you don’t need to stress.
Quartile.inc uses the exact same syntax as the quartile function we already discussed. It uses the values of 0 to 4 to represent each quartile inclusive of the minimum and maximum values.
The syntax for quartile.exc is similar but only uses the values of 1 to 3 as it doesn’t recognize the minimum and maximum numbers of the dataset as part of its function.
That means you will get a completely different result if you use the .exc version.
To make it simple, just remember the quartile.inc function includes the min and max of the dataset and the quartile.exc function excludes them.
What Do Percentiles Mean When Talking About Quartiles?
As quartiles divide data up into 4 sections they are sometimes referred to as percentiles in groups of 25% (25% x 4 = 100%).
 The first quartile is called the 25th percentile as 25% of the data lies beneath this number (represented by 1 in the Google Sheets formula)
 The second quartile or median is the 50th percentile as 50% of the data is below this point (2 in the syntax formula)
 The third quartile is the 75th percentile as 75% of the data lies beneath this point (3 in the Sheets syntax)
Conclusion
After reading this article, you should now know how to find quartiles in Google Sheets. Let us know in the comments if you have any questions and check out our other articles to become a spreadsheet master!
The Excel MEDIAN function returns the median (middle number) in the supplied set of data. For example, =MEDIAN(1,2,3,4,5) returns 3.
 number1 – A number or cell reference that refers to numeric values.
 number2 – [optional] A number or cell reference that refers to numeric values.
The MEDIAN function returns the median (middle number) in a set of data. The calculation performed by MEDIAN varies according to the number of numeric values provided. When the number is odd, MEDIAN returns the middle number in the group. When the number is even, MEDIAN returns the average of the two numbers in the middle.
The MEDIAN function takes multiple arguments in the form number1, number2, number3, etc. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MEDIAN ignores empty cells, text values, and the logical values TRUE and FALSE. The MEDIAN function will accept up to 255 separate arguments.
Examples
When the number of values provided is odd, MEDIAN returns the middle number:
When the number of values provided is even, MEDIAN returns the average of the two middle numbers:
In the worksheet shown above, the formulas in H5 and H6 are:
Note that MEDIAN ignores the empty cell in D5:D16 and returns the middle number in the eleven values provided.
How to median values ignore zeros or errors in Excel?
In Excel, when we apply the common formula =MEDIAN(range), it will calculate the median value within a range including zeros, and it will also get error result when is applied in a range which includes error values as below screenshot show. In this tutorial, I am going to talking about how to medina ignoring zeros or errors in Excel.
Median ignore zeros
To median a range ignoring zero values, you can apply below formula.
Select a cell that you will put the median result into, C2 for instance, type this formula =MEDIAN(IF(A2:A17<>0,A2:A17)) , press Shift + Ctrl + Enter keys. See screenshot:
In the formula, A2:A17 is the range you want to median excluding zeros.
Median ignore errors
To median range values ignoring error values, you can do as below:
Select a cell which you will place the median result into, enter this formula =MEDIAN(IF(ISNUMBER(F2:F17),F2:F17)) , press Shift + Ctrl + Enter keys. See screenshot:
Learning how to calculate a median in Excel 2013 is similar to learning how to perform most other mathematical functions within the program, such as subtracting in Excel.
If you are new to using formulas in Excel, then this article can be helpful. Excel uses a formula that takes the values in a range of cells, then automatically determines the median from those values.
You can find a median in Excel 2013 by clicking in the cell where you want the median, typing the =median(AA:BB) formula (but replacing those placeholders with the actual start and end of the cell range,) then pressing Enter on your keyboard.
If you are not sure what a median value is, then you can find out more about it on Wikipedia.
A median can be a good alternative to an average if you find that your data values are highly skewed, and an average is not representative of the data that you are evaluating.
Your median value can be determined with just a few short steps in Excel, which can be a real timesaver when you are dealing with a lot of data values.
How to Determine a Median in Microsoft Excel 2013
 Open the spreadsheet.
 Select a cell to display the median.
 Type =MEDIAN(AA:BB).
 Replace AA and BB with the start and end of the cell range.
 Press Enter.
Our guide continues below with additional information on calculating Excel medians, including pictures of these steps.
How to Find an Excel Median with a Formula (Guide with Pictures)
This article will show you how to find the median value for a range of cells that you select. The median will be displayed in the cell that you choose in the second step of our tutorial.
Step 1: Open the spreadsheet containing the values for which you want to calculate a median value.
Step 2: Click inside the cell where you want the median to be displayed.
Step 3: Type =MEDIAN(AA:BB) into the cell, where AA is the cell location of the first cell of your range, and BB is the cell location of the last cell.
In my example image below, the formula would be =MEDIAN(A1:A7). You can then press Enter on your keyboard to calculate the formula.
Our tutorial continues below with additional discussion on Microsoft Excel medians.
More Information on Finding a Median in Excel 2013
Note that the median will be displayed in the cell, but that you can still view the formula by clicking on the cell, then looking at the Formula Bar above the spreadsheet.
If the median value of your data is not helpful, then you can also calculate an average in a similar manner.
Since we are using a formula to determine the median in this article, then making a change to one of the values in the cell range will also affect the displayed median. This is useful in that you don’t need to also adjust the median, since excel is simply going to recalculate it on its own. However, it can be problematic if you don’t want the median to change.
If you don’t want the median to change when you adjust one of the values in the formula range, then you will need to replace the median formula with the median value. You can do this by either clicking on the cell with the formula and typing the value shown in the cell, or you can cut the formula and paste only the value back into the cell.
If you add a new row or a new column that falls within the range of the median formula, then the formula will adjust to include that new row or column.
Additional Sources
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.
Some of the most widely used statistics are “measures of center,” which describe where the data is centered. This type of statistic is extremely useful, as it allows you to summarize all the data with one number/category. Statistics that summarize key aspects of the data are called summary statistics.
You can think of a measure of center as a “best guess”. If you had one guess at the value of a new observation (e.g. the height of a new student who joins the class), what would you guess?
The Three Measures of Center
The mean is the standard form of averaging.
The mean is calculated by adding up all the values in the data, and dividing by the number of data points.
The mean is only defined for quantitative variables.
Sometimes the mean is referred to simply as the “average” (for example in Sheets), but it is better to remember it as the mean.
The median is the middle value.
If you order all the data points from lowest to highest, the median is the value that sits directly in the middle.
If there are two “middle values” (which occurs when there is an even number of data points), the median is halfway between the two values.
The median is only defined for quantitative variables.
The median is the value such that half the values are below it, and half the values are above it.
The mode is the most commonly occurring value in the data.
If you count the number of times each category occurs, the mode is the category with the highest count.
Sometimes the mode is referred to simply as the most common value.
A dataset can have multiple modes – if multiple categories have the same count, and this count is higher than those of the other categories.
The mode is only defined for categorical variables.
When handling different types of data, some measures of center can tell you more useful information than others can. Whenever you have a categorical variable, the mode is your only choice for a measure of center. However, with quantitative variables, either the mean or median can be used to describe the “best guess”. You can begin building intuition about which of the three measures of center is most appropriate through some examples.
Q1: You have a dataset on the birth country of all of your students. A new student joins your class, and you want to take a “best guess” at where she comes from. What measure of center would you use?
Incorrect: What kind of variable is birth country?
Incorrect: What kind of variable is birth country?
Q2: You want to do a study on whether it is more expensive to rent an apartment in Seattle or in New York City. What data would you collect to answer this question, and what summary statistics could be useful?
The mean, median, and mode functions in Sheets all have the exact same syntax as the MIN and MAX functions, defined earlier here . You can either input all relevant values into the function separated by commas, or you can define a cell range. The latter is far more convenient in most cases.
Measures of Center in Sheets
The AVERAGE function returns the mean of a set of values. You can either input several values separated by a comma (e.g. =AVERAGE(value1, value2, value3) ), or you can input a range of cells of which you want to know the mean (e.g. =AVERAGE(A1:A10) ).
Note that mean is called AVERAGE in Sheets. It is nevertheless recommended to use the term “mean” to describe this measure of center wherever possible (e.g. in reports and articles), to disambiguate different measures of center. See here for a longer discussion.
The MEDIAN function returns the median of a set of values. You can either input several values separated by a comma (e.g. =MEDIAN(value1, value2, value3) ), or you can input a range of cells of which you want to know the median (e.g. =MEDIAN(A1:A10) ).
The MODE function returns the mode of a set of values. You can either input several values separated by a comma (e.g. =MODE(value1, value2, value3) ), or you can input a range of cells of which you want to know the mode (e.g. =MODE(A1:A10) ).
Example: Test Scores¶
Say you are helping grade for a class and your professor has given you a list of student scores for the last exam. How would you calculate the median and mode in sheets?
Q3: Given the sheet above, write a formula for the mean of the test scores.
Q4: Given the sheet above, write a formula for the median of the test scores.
Now that you have some practice with creating formulas to calculate median and mean, you can start to build some intuition as to what the differences between these measures of center may be. Say someone asked you for your advice about where they wanted to move after graduation, and that the weather was a major concern for them. You want to give them a summary statistic to accurately summarize what the weather might be like at those respective locations. Would the mean or median make more sense? The next example can help you understand when you would want to use the mean versus the median.
Example: Weather¶
First, calculate and compare the mean maximum daily temperature in Seattle and New York City (NYC). The data for the two cities’ temperatures are in two different sheets.
The “actual_max_temp” is in column D, and tells you the maximum daily temperature. Calculating the mean of that is as simple as using the AVERAGE function on that cell range as shown in the image below. From this, you can see that the mean maximum temperature in Seattle is 64.2 degrees.
You can now switch to the NYC sheet and use the exact same formula.
Q5: What is the mean maximum temperature in NYC? (Use 1 decimal point.)
This example indicates that on average, over the course of twelve months, Seattle and NYC have fairly similar temperatures. Does this seem right to you?
In reality, for a given time of year, the temperatures of Seattle and NYC usually differ significantly. NYC winters are considerably colder than Seattle winters, and NYC summers tend to be warmer than Seattle summers. When averaged over twelve months, however, these effects “cancelled out”, and, when looking just at the mean, it may look as if Seattle and NYC have similar temperatures all year round.
Sometimes summary statistics can oversummarize the data. You will learn more about how to take this oversummarization into account in the section below on measures of spread . In the meantime, you can look closer into investigating the median of this data.
Q6: Calculate the median maximum temperatures for Seattle and NYC. Do these statistics tell a different story? Why?
Right now, the mean and median may not seem all that different. However, there are cases where the median is more useful than the mean. The next section on outliers will explain this difference through an example on family income.
Software as service is a revolutionary service model of Cloud Computing that makes it possible for you to use a particular software without having to pay for its license and even without downloading and local installation. Some of these apps are available completely free of cost and to access them all you need is just a computing device with a browser and an Internet connection
From the Google suite of free cloud services, one such free but powerful tool is Google Sheets. In this article, I will show step by step how can you apply the basic 6 different math functions in Google Sheets.
The 6 basic functions I am going to implement are MAX, MIN, AVERAGE, COUNT, COUNTUNIQUE, and MEDIAN.
Data Preparation
To see these functions in action, first, I need to prepare some dummy data to which I will apply all these functions. Let’s prepare the necessary data with the help of the following steps.
Step 1: Visit Google Sheets and start a new blank sheet.
Step 2: Prepare data with two columns “Days” and “Temperature” in D and E respectively as shown in the following reference figure. Number the “Days” column from 1 to 10 and enter some dummy data in the “Temperature” column.
[Note: It is recommended to use the same columns, cells, and values as given in the reference figure for easy reference and results comparison]
1. “MAX()” Function
Step 1: The “MAX()” function can give us the maximum temperature observed in our data. To do so select cell E14.
Step 2: Insert the “MAX()” function by either clicking on the “Insert” menu, then pointing your cursor to the “Function” option, and then clicking on the “MAX” function or just typing in E14 “=MAX(”.
Step 3: Google Sheets will automatically select all the data in Column E. Since this selection is fine as per our example, just press the “Enter” button and cell E14 will now contain the maximum value present in our example data i.e. 41.
2. “MIN()” function
The “MIN()” function will give us the coolest or the minimum temperature present in our data. To see it in action, select cell E15 and type “=MIN(E2:E11)” and press “Enter“. E15 will not contain the minimum value in our data i.e. 5.
3. “AVERAGE()” function
The “Average()” function will calculate for us the average of our temperature data. To apply this function in cell E16, type “=Average(E2:E11)” and press “Enter”. E16 will now have the average temperature.
4. “COUNT()” function
The function “Count()” can tell us about the total number of observations in our temperature data. To use the “Count()” function type in E17, type “=Count(E2:E11)” and press the “Enter” button. This will give us 10 as a result in cell E17.
5. “COUNTUNIQUE()” function
Our fifth function “CountUnique()” can report for us the total number of unique observations in our data. To apply it, in cell E18 type “=CountUnique(E2:E11)” and then press “Enter“. E18 will now contain 9 as in our example data, two days (5 and 9) have the same temperature i.e. 33 so both are counted as 1.
6. “MEDIAN()” function
Our sixth and final function the “MEDIAN()” function can help us calculate the middle value in our data. To use it in cell E19 type “=MEDIAN(E2:E11)” and press “Enter”. E19 will now have the median value of our observation i.e. 25, close to the average value.
Wrapping Up
In this article, I have shown you step by step how can you apply 6 basic math functions in Google Sheets to a set of hypothetical temperature data quickly and easily.
Apply the same functions to a different set of data and let us know about the results in the comments section.
When analyzing numerical data, you may often be looking for some way to get the “typical” value. For this purpose, you can use the socalled measures of central tendency that represent a single value identifying the central position within a data set or, more technically, the middle or center in a statistical distribution. Sometimes, they are also classified as summary statistics.
The three main measures of central tendency are Mean, Median and Mode. They all are valid measures of central location, but each gives a different indication of a typical value, and under different circumstances some measures are more appropriate to use than others.
How to calculate mean in Excel
Arithmetic mean, also referred to as average, is probably the measure you are most familiar with. The mean is calculated by adding up a group of numbers and then dividing the sum by the count of those numbers.
For example, to calculate the mean of numbers <1, 2, 2, 3, 4, 6>, you add them up, and then divide the sum by 6, which yields 3: (1+2+2+3+4+6)/6=3.
In Microsoft Excel, the mean can be calculated by using one of the following functions:

– returns an average of numbers. – returns an average of cells with any data (numbers, Boolean and text values). – finds an average of numbers based on a single criterion. – finds an average of numbers based on multiple criteria.
For the indepth tutorials, please follow the above links. To get a conceptual idea of how these functions work, consider the following example.
In a sales report (please see the screenshot below), supposing you want to get the average of values in cells C2:C8. For this, use this simple formula:
To get the average of only “Banana” sales, use an AVERAGEIF formula:
=AVERAGEIF(A2:A8, “Banana”, C2:C8)
To calculate the mean based on 2 conditions, say, the average of “Banana” sales with the status “Delivered”, use AVERAGEIFS:
=AVERAGEIFS(C2:C8,A2:A8, “Banana”, B2:B8, “Delivered”)
You can also enter your conditions in separate cells, and reference those cells in your formulas, like this:
How to find median in Excel
Median is the middle value in a group of numbers, which are arranged in ascending or descending order, i.e. half the numbers are greater than the median and half the numbers are less than the median. For example, the median of the data set <1, 2, 2, 3, 4, 6, 9>is 3.
This works fine when there are an odd number of values in the group. But what if you have an even number of values? In this case, the median is the arithmetic mean (average) of the two middle values. For example, the median of <1, 2, 2, 3, 4, 6>is 2.5. To calculate it, you take the 3rd and 4th values in the data set and average them to get a median of 2.5.
In Microsoft Excel, a median is calculated by using the MEDIAN function. For example, to get the median of all amounts in our sales report, use this formula:
To make the example more illustrative, I’ve sorted the numbers in column C in ascending order (though it is not actually required for the Excel Median formula to work):
In contrast to average, Microsoft Excel does not provide any special function to calculate median with one or more conditions. However, you can “emulate” the functionality of MEDIANIF and MEDIANIFS by using a combination of two or more functions like shown in these examples:
How to calculate mode in Excel
Mode is the most frequently occurring value in the dataset. While the mean and median require some calculations, a mode value can be found simply by counting the number of times each value occurs.
For example, the mode of the set of values <1, 2, 2, 3, 4, 6>is 2. In Microsoft Excel, you can calculate a mode by using the function of the same name, the MODE function. For our sample data set, the formula goes as follows:
In situations when there are two or more modes in your data set, the Excel MODE function will return the lowest mode.
Mean vs. median: which is better?
Generally, there is no “best” measure of central tendency. Which measure to use mostly depends on the type of data you are working with as well as your understanding of the “typical value” you are attempting to estimate.
For a symmetrical distribution (in which values occur at regular frequencies), the mean, median and mode are the same. For a skewed distribution (where there are a small number of extremely high or low values), the three measures of central tendency may be different.
Since the mean is greatly affected by skewed data and outliers (nontypical values that are significantly different from the rest of the data), median is the preferred measure of central tendency for an asymmetrical distribution.
For instance, it is generally accepted that the median is better than the mean for calculating a typical salary. Why? The best way to understand this would be from an example. Please have a look at a few sample salaries for common jobs:
 Electrician – $20/hour
 Nurse – $26/hour
 Police officer – $47/hour
 Sales manager – $54/hour
 Manufacturing engineer – $63/hour
Now, let’s calculate the average (mean): add up the above numbers and divide by 5: (20+26+47+54+63)/5=42. So, the average wage is $42/hour. The median wage is $47/hour, and it is the police officer who earns it (1/2 wages are lower, and 1/2 are higher). Well, in this particular case the mean and median give similar numbers.
But let’s see what happens if we extend the list of wages by including a celebrity who earns, say, about $30 million/year, which is roughly $14,500/hour. Now, the average wage becomes $2,451.67/hour, a wage that no one earns! By contrast, the median is not significantly changed by this one outlier, it is $50.50/hour.
Agree, the median gives a better idea of what people typically earn because it is not so strongly affected by abnormal salaries.
This is how you calculate mean, median and mode in Excel. I thank you for reading and hope to see you on our blog next week!
This is a story about a bar, 10 regular folks, and the world’s richest man, to explore different measures of average in Google Sheets.
Somewhere along the way, we’ll seek to demonstrate the robustness of the different average measures, but more on that in a minute.
I want you to picture your favourite bar or pub.
For me, it might be a pint of ale at The Dickens Inn, near the River Thames in London:
I should just finish this blog post here, and we could all spend the rest of the day in happy reverie, supping our favourite tipple.
Alas, that won’t do! We have work to do and things to learn, so let’s get started.
The dataset
Imagine ten friends, all regular folks, sitting at the bar, eating and drinking, chatting and laughing. A most convivial scene. The beer tastes delicious of course, the floor is dappled with sunlight and the comforting aroma of Pie & Mash wafts by their nostrils. Anyway, I digress.
Let us play a little game. Our subjects don’t mind because they’re fictional.
We ask them all to write down their salaries in our Google Sheet, so we have the following results:
Good. That’s our dataset.
Calculating the average in Google Sheets
You can use formulas or pivot tables to calculate averages. In this post, I’ll show the method using formulas, since it makes it easier to focus on what’s happening with the average measures.
We then calculate the “average” salary, using the three different measures we know:
Mean Average in Google Sheets
Using the formula
we calculate the mean — the classic average — of our data. This calculation is the total of all the values divided by the count of how many there are.
The mean of this dataset is $66,170
It’s the measure of “middleness” or “centralness” that we’re perhaps most familiar with.
However, there are two other common average measures:
Median Average in Google Sheets
Using the formula
we calculate the median, or middle value, of our data.
If we have an odd number of values, this value is just the middle value that bisects our data into two evenly numbered groups.
If we have an even number of values, as we do in this example with 10 people, then the median is the mean of the middle two values.
In our case, the middle two values, when the data is sorted, are $64,500 and $66,400. We add these two together, which is $130,900, and divide them by two to give us the median value.
The median of this dataset is $65,450
Mode Average in Google Sheets
Using the formula
we calculate the mode — the most frequent value — in our our data.
The mode of this dataset is $67,000
Note: if none of the values in your dataset occur more than once, then no mode can be calculated and the Google Sheets function will produce an error:
This is where we introduce the world’s richest man.
His name is Jeff Bezos and he’s worth approximately $120bn, that’s right 120 BILLION DOLLARS. It’s hard to fathom how much money that is, but suffice to say that quitting his lucrative Wall Street job to found Amazon 23 years ago has paid off handsomely.
For the sake of this exercise, we’re going to assume Jeff has an annual salary of $10 million dollars (the majority of his $120bn wealth is his ownership stake in Amazon).
Now, suppose Jeff has just finished some stressful meetings in London and decides to avail himself of a pint of beer. He just happens to choose the same pub as our 10 friends from earlier.
After the awkward ensuing silence and subsequent predictable astonished whispers (“He looks like… Is that…? Wait, is it really…?”), normal service resumes and Jeff pulls up a stool at the end of the bar.
He is subject number 11 in our dataset, which, if we include Jeff’s salary, now looks like this:
Whew, that’s an outlier if ever I saw one.
And it has a HUGE impact on one of our averages, but which one?
The effect of outliers on mean, median and mode
Taking our new dataset of 11 values above, let’s calculate the mean, median and mode again.
The new mean is $969,245
The new median is $66,400
The new mode is $67,000
Wow! Look at that mean value now.
It’s jumped from $66,170 to $969,245. Now, if we were to say the average salary of all the folks in this room was almost 1 million dollars, you’d jump to all sorts of wrong conclusions.
The mean has been skewed so dramatically by the outlier, that it’s become a rather meaningless number now. It’s highly sensitive to outliers.
However, look at the median, which has barely changed, and the mode, which hasn’t changed at all. The median and the mode are what we call robust statistics. They have not been skewed, or unduly affected, by the new outlier.
Average Conclusion
We’ve seen how the mean is sensitive to outliers, and this is its principal drawback. It’s not a robust statistic. As we saw in our example, it was skewed so much that the result was essentially meaningless.
The advantage of the mean is that it can be calculated on discrete (example above) and continuous data (e.g. people’s heights).
The median, being the middle value that bisects the dataset, is less affected by outliers, so is a better measure of central tendency when the data has outliers or is not symmetrical.
The mode, being the most frequent value, has the principal advantage that it can be calculated for numerical and categorical (nonnumerical) data.
It’s also possible to have no mode (no values occur more than once, which can happen with continuous data), two modes (bimodal distribution), or even many modes (multimodal).
entering the matching industry codes in the formula e.g. “100” does not work
either.
do i need to adjust cell format (now”‘general”)?bplu. @gmail.com
Did you really enter that formula as array formula (not only ENTER but
CTRL + SHIFT + ENTER)?myra_deV
but why does the MEDIANIF() function differ from the hands on median formula?
any ideas how i can use the MEDIANIF function to match on the first 3 digits
only and then take a median value?bplu. @gmail.com
If given the same data these functions should not differ.
Could you give a short example how your industry codes and your data
look like?Either post it here (anonymous data only) or send me an email.
myra_deV
sheet 2 is for the median value per industry
for code=1000 (cell A2)
using =MEDIANIF(sheet1!A2:A9;A2;sheet1!B2:B9)=
0,533133507=MEDIAN(sheet1!B2:B9)= 0,622604106
the median(if()) function gives this answer also.Since not all industry codes have enough data( i need at least 5) i want to
take medians of 100* or 10** as well. is this possible with a macro or
formula?
or shall i continue by hand 😉thnx in advance.
bplu. @gmail.com
The correct code for the UDF is IMHO:
Function MEDIANIF(ByVal rgeCriteria As Range, _
ByVal sCriteria As String, _
ByVal rgeMaxRange As Range) As SingleDim iconditioncolno As Integer
Dim inumberscolno As Integer
Dim lrowno As Long
Dim lmatch As Long
Dim arsngvalues() As Single
Dim sngmedian As Single
Dim bsorted As Booleaniconditioncolno = rgeCriteria.Column
inumberscolno = rgeMaxRange.Column
ReDim arsngvalues(rgeCriteria.Rows.Count)For lrowno = 1 To rgeCriteria.Rows.Count
If rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno – 1,
iconditioncolno).Value = sCriteria Then
lmatch = lmatch + 1
arsngvalues(lmatch) = rgeCriteria.Parent.Cells(rgeCriteria.Row
+ lrowno – 1, inumberscolno).Value
End If
Next lrowno
ReDim Preserve arsngvalues(lmatch)
Do
bsorted = True
For lrowno = 2 To lmatch
If arsngvalues(lrowno – 1) > arsngvalues(lrowno) Then
sngmedian = arsngvalues(lrowno – 1)
arsngvalues(lrowno – 1) = arsngvalues(lrowno)
arsngvalues(lrowno) = sngmedian
bsorted = False
End If
Next lrowno
Loop Until bsorted = TrueIf lmatch Mod 2 = 1 Then MEDIANIF = arsngvalues((lmatch + 1) / 2)
If lmatch Mod 2 = 0 Then MEDIANIF = (arsngvalues(lmatch / 2) +
arsngvalues(1 + lmatch / 2)) / 2
End FunctionIf you enter in sheet2:
A1:
1000
B1 (as array formula!):
=MEDIAN(IF(Sheet1!$A$3:$A$520=Sheet2!A1,Sheet1!$B$3:$B$520,””))
C1:
=MEDIAN(Sheet1!B3:B10)
D1:
=medianif(Sheet1!$A$3:$A$520,Sheet2!A1,Sheet1!$B$3:$B$520)
E1 (as array formula!):
=MEDIAN(IF(LEFT(Sheet1!$A$3:$A$520,3)=”100″,Sheet1!$B$3:$B$520,””))Then cells B1:E1 should all show the correct result 0.622604106. E1
gives you an example how to calculate a median of 100*. For 10* you can
use LEFT(. 2)=”10″, for example.