MODEThe MODE formula in Google Sheets returns the most frequently occurring value in a dataset. It is commonly used in statistical analysis to identify the value that occurs most frequently in a set of data. The formula requires at least one input value, but can accept multiple values to include in the analysis.
- How to use
- Examples of using
MODEformula not working?
- Similar formulas to
MODE formula with the syntax shown below, it has 1 required parameter and 1 optional parameter:
- value1 (required):
The first value or range of cells containing values to include in the analysis.
- value2, ... (optional):
Additional values or ranges of cells to include in the analysis.
ExamplesHere are a few example use cases that explain how to use the
MODE formula in Google Sheets.
Identify most common response in a survey
Use MODE to quickly identify the most common response to a survey question, such as a multiple choice question. Simply input the range of cells containing the responses and the formula will return the most frequently occurring response.
Find the mode of a set of exam scores
Input the range of cells containing exam scores and use MODE to identify the score that appeared most frequently. This can help identify any patterns or trends in the scores.
Analyze sales data
Use MODE to analyze sales data and identify the product that sells most frequently. Input the range of cells containing the sales data and the formula will return the product that appears most frequently.
MODE not working? Here are some common mistakes people make when using the
MODE Google Sheets Formula:
Using non-numeric values
The MODE formula only works with numeric values, so make sure you are not including non-numeric values in your range.
Using a range that includes empty cells
If your range includes empty cells, the MODE formula may return an error or an incorrect value. Make sure to exclude empty cells from your range.
Using a range that is too large
If your range is too large, the MODE formula may take a long time to calculate or may even cause Google Sheets to crash. Try to limit your range to a reasonable size.
Using a range that includes duplicates
If your range includes duplicate values, the MODE formula may return a value that is not meaningful. Make sure to remove duplicates from your range before using the formula.
Forgetting to close parentheses
Make sure to close all parentheses in your formula, otherwise Google Sheets will return an error.
The following functions are similar to
MODE or are often used with it in a formula:
The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.
MEDIANfunction returns the median (middle) value of a set of numbers. It is commonly used to find the middle value in a range of data points. If the number of data points is even, it returns the average of the two middle values. This function can be useful in statistical analysis and data visualization.
STDEVfunction calculates the standard deviation of a set of numbers. It measures the amount of variation or dispersion of a set of values from the average (mean) value. It is commonly used in statistics to determine the spread of a data set. The values can be supplied as individual cells, ranges, or constants.
COUNTformula returns the number of numeric values within a specified range of cells. It is commonly used to count the number of entries in a given range that meet specific criteria, such as counting the number of sales above a certain threshold.
You can learn more about the
MODE Google Sheets function on Google Support.