How to calculate maximum in MS Excel

The MAX function in Excel is a handy tool that simplifies the process of identifying the maximum value within a given range. In a set of values, it yields the greatest value. By mastering this fundamental Excel function, we can streamline our data analysis and make informed decisions based on the highest values in our datasets. Let’s delve into the steps of calculating the maximum value in Microsoft Excel.

Syntax

The basic syntax for the MAX function is the following:

Here, number1 and number2 represents the cell number or any numeric value. It’s worth noting that we have the flexibility to include additional cells as needed.

Note: Ranges or cell references can be used instead of explicit values.

Simple values

If we simply want to find out the maximum value between 10, 20, and 30, we will use the MAX function using the formula below:

List of cells

If we want to find MAX by providing the list of cells, we can use the formula below:

Range of cells

If we want to find MAX by providing the range of cells, we can use the formula below:

If we want to use the range of cells and want to skip a cell number, we can use the formula below:

The above formula calculates the maximum of cells A1, A2, A3, and A5.

Steps

The following is a step-by-step guide to using the MAX function:

1. Select the cell: Click the cell where we want the maximum to appear.

2. Use the MAX function: Type “=MAX(” into the selected cell.

3. Select the range: If you want to calculate the maximum of the number of cells in the range A1 to A5, there are two ways to achieve the desired result:

   1. Write “A1:A5” between parentheses and press “Enter.” The formula should look something like this: =MAX(A1:A5).

   2. You can also type “A1, A2, A3, A4, A5” between parentheses and press “Enter.” The formula should look something like this: =MAX(A1,A2,A3,A4,A5).

Let’s apply the above steps to our dataset. Let’s suppose we have the following dataset:

Now, our objective is to determine the maximum marks the student got in the required dataset. We have two ways to calculate the maximum. Let’s use both of them:

Calculating MAX with a list of cells

First, we select the cell where we need to calculate the MAX. Let’s suppose that the cell number is B9. After we select cell B9, we write the following in the cell:

Let’s visualize the above steps in action.

Note: As you can see in the above slides, when multiple cells are added, the system automatically highlights the cell number and alters its color for easier identification.

If an empty cell is included in a maximum formula and subsequently populated with a value, the value in cell B9, where the maximum is calculated, will automatically update to reflect the new value.

Calculating the MAX by specifying the range

First, we select the cell where we need to calculate the MAX. Let’s suppose that the cell number is B9. After we select cell B9, write the following in the cell:

Let’s visualize the above steps in action.

Things to remember

• If we try to find the product of any value (other than numbers) enclosed in double quotes, it will throw a #VALUE! error.

• Empty cells, text, the logical values TRUE and FALSE, and text values are all ignored when used with a range or by typing the cell numbers.