**How to Use the Excel-functions**

MAX and MIN to Find Values

MAX and MIN to Find Values

*Written by co-founder Kasper Langmann, Microsoft Office Specialist.*

Two of the more useful yet simple functions that Microsoft Excel offers are ‘MIN’ and ‘MAX’.

**These two functions find the smallest and largest value in an array.**

It could be an array of constants (literal values) or an array of cells (range of cells).

Let’s get into all the meaty details, there’s a lot of awesomeness to learn!

**Table of Content**

**Conclusion: Wrapping up**

**This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.*

**Breakdown of MIN’s syntax**

Let’s look at the syntax *(MIN and MAX use the same syntax)*…

‘=MIN(number1, [number2], …)’

**The function has one required argument, ‘number1’. **

All later arguments are optional.

**Did you know: Excel shows optional arguments in square brackets (such as above)**

Look at this example of the ‘MIN’ function.

‘=MIN(1,2,3,4,5)’

The result of this example would be ‘1’.

If we replace the ‘MIN’ with ‘MAX’ we would get ‘5’ as our result.

As you can see this is a pretty simple concept…

‘MIN’ and ‘MAX’ are prebuilt functions. So, you can also find them listed when you begin to type them into the formula bar.

When sorting your values in an array isn’t a good option, ‘MIN’ and ‘MAX’ can help.

The purpose of the previous example was to show the simplicity of using these functions.

**Using the ‘MIN’ function**

So, let’s look at a basic example of the ‘MIN’ function in action.

We can place literal values as arguments in the formula.

As you can see, the ‘MIN’ function finds ‘1’ to be the smallest value among the literal values in the formula.

But the ‘MIN’ function isn’t limited to working with just literal values.

You can also use it with cell references.

**Finding the lowest grade in the class**

Imagine we have some students in a class and we want to find the lowest grade.

We could sort the table by grade in ascending, or lowest to highest, order.

But the ‘MIN’ function is a better option. This will allow us to find the lowest grade without messing up the current order of the names.

Select the cell you want the lowest grade to appear in and type in the formula.

Since you have a range of values for the grades, you don’t need to type them in as literal values.

Instead, highlight the range of grade values in our data table.

Select B2 and drag the range down to B11.

Excel will place the range inside the parentheses of the formula.

This highlights the simplicity of the ‘MIN’ function. Imagine a data set with hundreds or thousands of rows.

Now, you just need to close the parentheses and press ‘Enter’. The result is the lowest grade in the range!

**Mixing cells references with literal values**

You can actually mix cell references and literal values in the ‘MIN’ function.

Let’s say you want to add the value ‘70’ to your array of values to calculate the minimum of.

We already saw that the lowest grade in the range B2:B11 was ‘75’.

But now we should expect the ‘MIN’ function to return ‘70’.

**Using the ‘MAX’ function**

All the same concepts about the ‘MIN’ function hold true for the ‘MAX’ function.

So, let’s look at some examples.

We can use the ‘MAX’ function to find the largest value in a range of numbers. The syntax for ‘MAX’ is exactly the same as that for ‘MIN’.

Since we have already covered that, we won’t cover it again. You can refer to the introduction to the ‘MIN’ function if necessary.

**But let’s look at an example using ‘MAX’ with a list or literal values.**

See how the ‘MAX’ function finds ‘5’ to be the maximum value among the literal values in the formula.

*Tip: Make sure to check out the new functions MAX IF and MAXIFS.*

But the ‘MAX’ function isn’t limited to working with just literal values.

You can also use it with cell references instead of literal values.

**Finding the highest grade in class**

Referring back to our student data, we now want to now find the highest grade.

We want to maintain the alphabetical order of the names.

Sorting on values to seek out the highest grade is not viable.

So, let’s impose the ‘MAX’ function on the array of cell values containing all the grades! Here’s how…

We will select the entire range B2:B11 by clicking and dragging down.

Recall how we did this with the ‘MIN’ function.

Our formula returns the value ‘94’. This is the highest grade in the data set.

Now that we know the highest grade, let’s use the ‘MIN’ function to find the lowest grade.

It’s the same exact procedure. We just change the function to ‘MIN’.

**Selecting multiple ranges**

What if you wanted to find the highest grade among the female students in this data set?

You can do this by selecting multiple ranges for your arguments in the ‘MAX’ formula.

This holds true for ‘MIN’ as well.

Just select your first range. This could be one cell or more than one cell. Then separate this range from the next with a comma. It should look something like the following figure.

**In this example, we have a mix that includes a range of multiple cells as well as single cells.**

This illustrates how to use the ‘MAX’ (and ‘MIN’) functions when dealing with non-contiguous data.

The result for this example indicates that the highest grade among females is 93.

This is not the same as the highest grade overall.

**Mixing literal values with cell references**

Remember from our earlier examples for ‘MIN’ that you can mix literal values with cell references.

This works with ‘MAX’ also.

**So, let’s add the literal value ‘100’ to our array of cell references from the student data table.**

Now our result will be different than before.

Recall that the highest grade in the class was ‘94’.

Now the highest value in the ‘MAX’ formula is ‘100’.

**Thinking outside the ‘MIN’/’MAX’ box**

There’s one thing you can do to speed up your progress when learning new functions…

That is to try using the function in different ways. Imagine challenges where it might be useful, but not intuitive, to use a specific function.

This might require considering other functions that give you numeric results.

For instance, the ‘LEN’ function returns the length of string data in a cell.

Place a literal text string enclosed in double quotes between the parentheses of the ‘LEN’ formula.

**Then it will tell you the number of characters.**

You can also use cell references.

That’s exactly what I’m going to do to show you how to use ‘MAX’ and ‘MIN’ with another function

Sometimes you are working with numeric values like grades or dollar amounts.

But sometimes you may think of a different challenge that you are looking for a solution to.

This may require an extra step to produce number values. These values help you answer certain questions.

**In this case, the questions are what is the smallest or largest string value by length? **

This is just a simple example to help you think about different scenarios. There are many where ‘MIN’ and ‘MAX’ prove their value. These are simple functions to learn and use, but knowing where you can apply them takes a bit more thought.

**Wrapping up**

The ‘MIN’ and ‘MAX’ functions are two of the simplest of the pre-built functions in Excel. Learning them is easy.

Remember that you can use both with literal values as well as cell references. You can also use them with ranges, either contiguous or otherwise.

Mixing cell references, ranges, and literal values also illustrates the flexibility these functions offer.

While these two functions are both simple and flexible, they are also quite powerful.

**Remember that you can use them with the results of other functions and formulas. **

Don’t limit yourself to using them with primary data only!