Guide: Establishing Upper & Lower Boundaries with Excel's Range Limits

Guide: Establishing Upper & Lower Boundaries with Excel’s Range Limits

Quick Links

When using Excel, you can create formulas that produce a minimum value or maximum value in a calculation. In this article, we will show you the potential uses of this simple Excel formula and how to implement it in your spreadsheet.

Finding Minimum and Maximum Values in Excel

A more well-known function of Excel lets you find the highest and/or lowest values within a range of numbers, handy if you are keeping track of bills in your budget. You can also combine these formulas to find the range (the largest minus the smallest) within that list of numbers if you’re looking to produce statistical data. Both of these make use of the MIN and MAX functions in Excel.

However, a less-known use of the MIN and MAX functions in Excel lets you cap numbers in a spreadsheet, or set a minimum value. Let’s see how we can do this.

How to Produce a Result With a Minimum Value in Excel

To produce a result with a minimum value in Excel, use the following formula:

=MAX(X,Y)

where X is the numerical value or cell reference that determines the minimum value, and Y is the cell reference of the number you are looking to affect. Let’s look at how to actually use it.

Let’s say we own a grocery store, and we take pre-orders from our customers. Now that we have compiled the total number of orders for each fruit, we need to place the order with our wholesaler. However, the wholesaler insists that we order a minimum of 1500 for each fruit.

Therefore, we want Excel to tell us how many pieces of each fruit we need to order from the wholesale to both satisfy our customers’ orders and meet the minimum requirement of the wholesaler. Begin by clicking the cell where you want the first calculation to be made (in this case, we’re starting with bananas).

Start to type the following formula:

=MAX(

This might seem contradictory, as your intention is to provide a minimum result of 1500 in the final column, not a maximum result. However, the reason we type MAX is that we are telling Excel to find the maximum possible value in the calculation, with no upper limit.

Next, inside the parentheses, we need to type the criterion (in this case, it’s 1500):

=MAX(1500

Finally, we need to add a comma, click or type the cell reference we are looking to affect (in our example, it’s cell K20), and then press Enter:

=MAX(1500,J20)

You can now see the number of bananas we need to order from the wholesaler.

If the minimum criterion is likely to change, instead of typing the minimum number into the formula above, we would type the minimum value into another cell and use an absolute reference to tell Excel where to find the criterion:

=MAX($K$28,J20)

If you were to change “1500” to another number in cell K28, the minimum criterion would automatically change within your calculations in your table.

We can now apply the same formula to the other cells in the table by using Excel’s AutoFill function .

Forex Robotron Gold Package

We have now fulfilled our customers’ orders while also meeting the wholesaler’s minimum requirements for each fruit.

How to Produce a Result With a Maximum Value in Excel

To produce a result with a minimum value in Excel, use the following formula:

=MIN(X,Y)

where X is the numerical value or cell reference that determines the maximum value, and Y is the cell reference of the number you are looking to affect. Let’s look at this in more detail.

Let’s assume we own a business and offer bonus incentives to our employees. However, we want to cap the bonuses they can receive every two months to $5000.

Firstly, type the following formula in the cell where you want the result to show:

=MIN(

Again, this might seem confusing, as we are aiming to produce a capped value in the final column, not a minimum value. However, we type MIN because we are telling Excel to find the smallest possible value, with no lower limit.

Next, inside the parentheses, type the criterion (in this example, it’s 5000):

=MIN(5000

Finally, add a comma, click or type the cell reference to be affected (in this case, we would click on cell M20), and press Enter.

=MIN(5000,L20)

We can now see the maximum result for the first calculation we want to make:

If you are likely to change the value of the capped number, type the maximum value in another cell and reference this cell using an absolute reference , instead of typing the maximum value directly into the formula:

=MIN($M$28,L20)

As a result, if you were to amend the cap of 5000 in cell M28, the calculations in your table would automatically adjust to your new figure.

Then, use AutoFill to apply the formula to the remaining cells.

How to Produce Maximum or Minimum Results With Other Formulas in Excel

Using a formula within your MIN or MAX calculation can help you to present your spreadsheet more succinctly. Let’s say we want to remove the total bonus column altogether in the following table, and get Excel to work out the payment at the same time as considering the maximum value.

Therefore, our table would initially look like this, and we need to tell Excel to add the totals together for each employee to work out what will go in the final column:

To work out how much we’re paying Tom, in the cell where you want the total to show (L20 in this example), type the following formula and press Enter:

=MIN(5000,(SUM(J20+K20)))

See the previous sections for further information on how this formula works.

You can also type the criterion in another cell and use absolute referencing to link to that cell, rather than the value itself, within your formula, and press Enter:

=MIN($L$28,(SUM(J20+K20)))

Now, if you were to amend your cap of 5000 in cell L28, your formula would automatically pick up the newly inserted number.

Finally, use AutoFill to complete your table.

The same method can be used for calculating a maximum number or a minimum number.

Now you know how to cap results or set a minimum value in your Excel spreadsheet!

Win Answers