Fri. Apr 19th, 2024


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.

Excel sheet containing a table with three columns. The first column contains fruit products, the second column contains customer orders, and the third column (blank) contains orders to make with the wholesaler (minimum 1500).

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.

Excel sheet showing the use of the MAX formula to produce a minimum result of 1500 in the final column.

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)

Excel sheet showing the minimum value in cell K28 and the correct formula to use to produce a minimum result in the final column of the table.

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.

Excel sheet showing the total orders in the rightmost column after having applied the minimum value and used AutoFill.

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.

Excel sheet containing a table with five columns. The first column contains employee names, the second and third columns contain the respective bonuses to be paid for January and February, the fourth column contains the total bonuses, and the fifth column is headed 'Maximum bonus: 5000' with no values.

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:

Excel sheet showing the use of the MIN formula to produce a maximum result of 5000 in the final column.

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)

Excel sheet showing the maximum value in cell M28 and the correct formula to use to produce a minimum result in the final column of the table.

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.

Excel sheet showing the total bonus in the rightmost column after having applied the maximum value and used AutoFill.

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.

Excel sheet containing a table with five columns. The first column contains employee names, the second and third columns contain the respective bonuses to be paid for January and February, the fourth column contains the total bonuses, and the fifth column is headed 'Maximum bonus: 5000' with no values.

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:

Excel sheet containing a table with four columns. The first column contains employee names, the second and third columns contain the respective bonuses to be paid for January and February, and the fourth column is headed 'Max 5000' with no values.

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.

Excel sheet showing the first result for a calculation where the values in two columns have been added together and a maximum has been applied.

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)))

Excel sheet showing the maximum value in cell L28 and the correct formula to use to produce a maximum result in the final column of the table.

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.

Excel sheet showing the totals in the rightmost column after having performed a calculation, applied the maximum value, and used AutoFill in that column.

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!



Source link

By John P.

Leave a Reply

Your email address will not be published. Required fields are marked *