How to calculate mean, mode, and median in Excel step by step

when you work with data in excelSooner or later you need to know what the typical or representative value from that set of numbers. That's where three key elements of basic statistics come into play: mean, median, and modeThese are concepts that are explained a lot in theory, but in everyday life they are mainly used within a spreadsheet.

Although they usually go hand in hand, each of these measures of central tendency contributes a a different way of summarizing a data setThe good news is that in Excel you don't need to do the calculations by hand: there are specific functions like AVERAGE, MEDIAN, MODE.ONE and MODE.MULTIPLE that automate the entire process. Let's take a look, calmly and with many examples, at how they work, what each one returns, and in which cases it's more advantageous to use one or the other.

What are mean, median, and mode, and what are they used for?

In statistics we talk about measures of central tendency This refers to values ​​that attempt to describe, with a single number, the "center" of a data distribution. In other words, a value that summarizes how the set generally behaves, without having to look at each data point individually.

The three basic measurements that are almost always used are the arithmetic mean, median, and modeAlthough they are sometimes confused, in reality each one is calculated differently and answers a different question about the data you have in front of you.

• Fashion: is the value that appears most often in the set.
• Media: is the average, the sum of all values ​​divided by the amount of data.
• Median: is the value located right in the middle when you order the data from smallest to largest.

In a perfectly symmetrical distribution (for example, when the data is evenly distributed around the center), mean, median, and mode usually coincide or are very similarBut in skewed distributions or those with extreme values ​​(outliers) they can differ considerably, and that's when choosing one measure or the other makes all the difference.

Excel incorporates specific functions for each case: AVERAGE for the average, MEDIAN for the central value and a set of functions for the mode (FASHION, FASHION.ONE and FASHION.VARIOUS) that allow you to identify one or more of the most frequent values ​​in your data.

How to calculate the mean (average) in Excel

La mediaThe mean, also known as the average or arithmetic mean, is obtained by adding all the numbers in a set and dividing that sum by the total number of valuesIt is the most intuitive way to summarize a set of data.

For example, if you have the series {2, 2, 5, 9, 10}, you first add up all the values ​​(2+2+5+9+10 = 28) and then divide by the number of data points, which in this case is 5. The result is 28 / 5 = 5,6That 5,6 is the average of that specific set.

In Excel you don't need to manually perform the addition and division; simply use the function AVERAGEwhich handles all the calculations in one go. The basic syntax you need to remember is very simple:

=AVERAGE(number1; ; …)

The first argument, number1This is mandatory, and from there you can add more numbers, cell references, or entire ranges. Excel supports up to 255 arguments in this function, so you can work with fairly large datasets without any problems.

Imagine you have registered the monthly rainfall of a year in the range C3:C14 (one row per month). If you want to know the average annual rainfall, just type the following into an empty cell:

=AVERAGE(C3:C14)

Excel will automatically sum all the values ​​in the range C3:C14 and divide the result by the number of cells with data in that range. It's a quick way to get the average of a time series without using a calculator.

There is another equivalent way to calculate the mean using two well-known functions: SUM y TELLInstead of directly applying AVERAGE, you could do:

=SUM(C3:C14) / COUNT(C3:C14)

This formula does exactly the same thing as AVERAGE: it sums all the values ​​in the range and divides by the number of cells containing numbers. Which method you use will depend on your preference and whether you need to integrate the calculation into more complex formulas.

Note that AVERAGE only consider the numerical valuesEmpty cells, text, or errors are not considered when calculating the average. This is especially useful when working with tables that contain gaps or explanatory notes.

What is the median and how is it calculated using the MEDIAN function?

La median is the measure that indicates the central point of an ordered data setTo obtain it, you would first have to order all the values ​​from smallest to largest and then locate the value that is right in the middle.

If the total number of data points is oddThe median is the value that falls exactly in the middle, so that you have the same number of values ​​on either side of a set. For example, if you have {1, 2, 3, 4, 5, 6, 7}, the median is 4because there are three values ​​below (1, 2, 3) and three above (5, 6, 7).

If instead the set has a quantity by When there are multiple elements, there is no single central value. In that case, the median is defined as the average of the two central values once ordered. For example, with the data {1, 2, 3, 5, 7, 10}, the central values ​​are 3 and 5, and the median will be (3+5)/2 = 4.

This functionality makes the median especially useful when you want to have a a representative measure that is not as affected by extreme valuesIf you add a very large number to the previous set, for example 1000, the mean would skyrocket, but the median would hardly change.

In Excel, this whole process is simplified with the function MEDIANThis calculates the middle value without you having to sort anything manually. The general syntax is:

=MEDIAN(number1; ; …)

You can enter individual numbers, cell references, or full rangesExcel allows, as with other statistical functions, up to 255 arguments for the MEDIAN function.

Following the example of monthly rainfall in C3:C14, if you want to know the central rainfall value (not the average), in another cell you would write:

=MEDIAN(C3:C14)

Excel will internally search for the middle value in the set. If the number of months considered is odd, the median will be one of those values; if it is even, Excel will calculate the average of the two central values from the range and it will return that result.

The official Excel documentation clarifies some important details about how it handles arguments MEDIAN:

• The arguments can be numbers, names, matrices or references that contain numbers.
• If you write logical values ​​(TRUE/FALSE) or numbers as text directly into the argument list, Excel may recognize them, but if they are within an array or range, it will not recognize them. They ignore those texts and logical values..
• Empty cells are omitted, while cells with a value 0 are included in the calculation.
• If any argument is a error or a text that cannot be converted to a number, the function will return an error.

To see this with a simple example, look at the data {1, 2, 3, 4, 5, 6} entered in the range A2:A7. If you type:

=MEDIAN(A2:A6)

You're taking the first five values ​​(1, 2, 3, 4, 5). Since there are 5 numbers, the third one is the middle one, so MEDIAN returns 3If instead you use the entire range up to A7:

=MEDIAN(A2:A7)

There are now six values ​​(1, 2, 3, 4, 5, 6). The function calculates the midpoint between the third and fourth numbers: (3 + 4) / 2 = 3,5.

In practice, the median usually remains near the averageHowever, it doesn't necessarily have to coincide with it. In highly skewed distributions or when there are very large or very small extreme numbers, the median can offer a more accurate view of the "typical position" of the data.

What is fashion and the functions of FASHION, FASHION.ONE and FASHION.VARIOUS

La fashion It is the measure of central tendency that answers the question: “Which value is repeated most often?”It doesn't care about the magnitude of the values ​​or their position in the set, only the frequency with which they appear.

If you have the set {2, 3, 3, 5, 7, 10}, the mode is 3because it is the number that appears most frequently (twice). In another example, if the values ​​were {1, 2, 2, 3, 3, 4}, you would have two modes: 2 and 3, since both are repeated the same number of times.

In Excel, the function was traditionally used FASHION to obtain the most frequent value from a list of numbers. Its syntax is very simple:

=MODE(number1; ; …)

For example, imagine you have a range of exam grades in C4:F19, and you want to know which grade is repeated most often. You could write:

=MODE(C4:F19)

If the value that appears most often is 9, the function will return 9 as the mode of that data set. This function is very useful for detecting the most common value in a series of measurements, scores, sales quantities, etc.

From Excel 2010The classic MODE function is considered legacy, and two new functions with clearer and more precise behavior have been incorporated: FASHION.ONE y FASHION.VARIOUSThe old fashion still exists because compatibility with old booksHowever, Microsoft recommends using the new features, as MODA may disappear in future versions.

MODE.ONE function: obtain a single mode

The function FASHION.ONE It is used when you want to know the value that is repeated most frequently in a range of data and you only need one result, even though there could be more equally frequent values.

Its syntax is:

=MODE.ONE(number1; ; …)

As with other statistical functions, you can pass it individual numbers, cell references, or entire ranges. For example, if your annual rainfall data is in cells C3:C14 and you want to know the most frequent amount, you can type:

=MODE.ONE(C3:C14)

Let's assume that the result returned by Excel is 110This means that the amount of rainfall of 110 (mm, liters, whatever unit you use) is the one that appears most often throughout the year. That will be the main trend according to MODA.UNO.

However, it may be the case that there is more than one value repeated the same number of timesFor example, that in that range the value 90 is also repeated the same number of times as 110. In that situation, MODA.UNO only returns one of the fashionsSpecifically, it detects the first value it finds that meets the maximum frequency. In other words, it doesn't automatically detect all multiple modes, only one.

This limitation is solved with the function FASHION.VARIOUS, designed precisely for situations where we have more than one trend in the dataset.

MODE.VARIOUS function: working with multimodal distributions

The function FASHION.VARIOUS It is designed to return all the fashions from a set of data, not just one. That is, instead of a single value, it returns a vertical matrix with all the numbers that share the highest frequency of occurrence.

Its syntax is similar to the rest of the fashion functions:

=MODE.MULTIPLE(number1; ; …)

The key here is not so much how the function is written, but in how to enter it into the spreadsheetbecause MODE.MULTIPLE returns multiple results at once. In versions prior to Excel 365, it was used as matrix formula, whereas in modern versions with dynamic matrices it behaves more automatically.

In the classic case of matrix formulas, the steps would be:

1. Select a range of cells with as many rows as results you expect to obtain (for example, two cells vertically if you think there are two modes).
2. Write the formula =MODE.MULTIPLE(C3:C14) without keys.
3. Instead of just pressing Enter, confirm the formula with Ctrl + Shift + EnterExcel will display the formula between curly braces `{}` and fill the selected cells with the different modal values.

Following the example of rainfall in C3:C14, if you have two modes (110 and 90), when you enter the function in this way you will see that FASHION.VARIOUS It returns both values ​​in the selected cells, one below the other, thus showing all the quantities that share the highest frequency.

If you don't know in advance how many fashions there are, you can select a slightly larger range of what is necessary, for example from E3 to E13, and then enter the matrix formula:

{=MODE.VARIOUS(B3:B23)}

The first cells in the range will display the actual mode values. The remaining cells (until the selection is complete) will show the error. #ATindicating that there are no more frequent values ​​to return. This way you know, for example, that in that specific set there are three fashions if three different numbers appear followed by several #N/A.

In modern versions of Excel (with dynamic arrays), simply type =MODE.MULT(range) Enter the values ​​in a single cell and press Enter. Excel will automatically "spill" the different modal values ​​downwards, occupying as many cells as necessary to display all the results.

If, for presentation reasons, you prefer to have the latest trends in horizontal Instead of vertically, you can combine FASHION.VARIOUS with the function TRANSPOSEThe process would be:

=TRANSPOSE(MODE.SEVERAL(B3:B23))

Again, the specific behavior (whether you need Ctrl+Shift+Enter or not) will depend on the version of Excel you're using. But the idea is that TRANSPOSE converts the vertical array returned by MODA.VARIOS into a horizontal array, distributing the modes along a row instead of a column.

Practical differences between mean, median, and mode in Excel

Although all three measures serve to summarize the information in a dataset, they don't always provide the same perspective. when to use each one It is key to making your Excel analyses more reliable.

La average It is very useful when the data is more or less balanced and there are no exaggerated extreme values. Think, for example, of a student's average performance over several terms or the average temperature of a city over a whole year.

La median (MEDIAN) It becomes more important when you suspect there are anomalies that could distort the average. In analyses of income, housing prices, or waiting times, the median usually reflects the average better. typical situation in most cases, since it ignores extreme values ​​to some extent.

La fashion (FASHION, FASHION.ONE, FASHION.VARIOUS) It focuses on frequency. It is ideal for answering questions such as: "What value appears most frequently?", for example, the best-selling clothing size, the most repeated grade in a set of exams, or the most common amount of rainfall in a region.

In a perfectly symmetrical distribution of values, the three measures tend to be very similar: mean ≈ median ≈ modeIn contrast, when the distribution is skewed (for example, when there are many small values ​​and a few gigantic ones), the mean can be quite far from the median, and the mode may even be in a completely different area of ​​the scale of values.

Excel lets you combine all these functions in your analyses. In a single table, you can calculate, for the same range of data, AVERAGE, MEDIAN and MODE.ONE/MODE.SEVERAL to get a complete picture of how the set behaves, detecting both the typical value, the central point and the most common value or values.

Mastering these functions (AVERAGE, MEDIAN, MODE.ONE, and MODE.MULTIPLE) will allow you to work much more effectively with data in Excel, whether you're creating simple reports or tackling more serious statistical analyses. Once you've internalized them, you'll see that use the appropriate function in each case It saves you time and helps you better interpret the information you work with on a daily basis.