How to Calculate Midpoint on Excel

Mean and median are two important mathematical terms can help determine the characteristics of a set of data. Mean is the average of a set of values while the median is the midpoint of a set of values. Fifty percent of the numbers are greater than the median and 50 percent of the numbers are less than the median. Excel has a built-in Median function that can calculate the median value of a set of worksheet cells.

Open an Excel worksheet that contains a column of numbers you wish to analyze. Click inside the first blank cell below the numbers you sorted, type "=Median(First_Cell:Last_Cell)" without quotes in the blank cell and press "Enter." For example, if the first cell is A1 and the last cell is A5, type "=Median(A1:A5)" in the blank cell and press "Enter." Excel computes the median for the numbers within the A1:A5 range and replaces the formula you typed with the median. The values A1:A5 between the parenthesis are arguments that the function accepts.

To edit a formula, click the cell that contains it and modify the formula in the bar that appears below the ribbon, to the right of the "fx" symbol. You can then update the function any way you like. For example, if you want to change the range of cells the formula's cell range from A1:A5, replace that range with a new one.

You can also change the function a formula uses. To find the mean of a group of cells, replace "=Median(First_Cell:Last_Cell)" with "=Mean(First_Cell:Last_Cell)." You can also pass a series of cells to the Median or Mean function as arguments. For example, you may type "=Median(A1, A3, A5)" in the cell where you want the median of those values to appear.

The mode is the number in a dataset that occurs most frequently. If your dataset consists of 2, 7, 9, 7 and 5, the mode is 7 because it occurs more than the other values. Median, mean and mode are the same in datasets that have a symmetrical distribution of values. They differ when the distribution is non-normal.

(Ref 2, find "symmetrical distribution" re this fact).

You can also pass arrays, references and names as arguments to the Median function. If you assign June_Sales as the name for a range of cells, you can type "=Median(June_Sales)" as your formula. When you type text representations of numbers into the argument list, Excel counts those values when it computes the median. However, error messages appear if the Median function cannot convert text values into numbers. Excel also computes the average of the two values in the middle of a dataset if it contains an even number of values. For example, if your dataset is 1, 2, 8 and 12, the median is the average of 2 and 8 – the dataset's two middle values.