How to Calculate Upper and Lower Limits in Excel (2 Methods)

Every now and then, there comes a situation where we need to know the upper and lower limits of a large dataset. This is one of the situations where Excel comes in very handy. In this article, we will learn how to calculate upper and lower limits in Excel in 2 different methods.

We shall use a sample dataset as an example to show. The information below offers the sales data of ten different super shops during the first six months of the year 2022.

Before starting the article, let’s have an overview of the final output you are going to get.

How to Calculate Upper and Lower Limits in Excel: 2 Suitable Methods

Let’s assume we have a dataset, namely Sales Data of Ten Supershops in 2022 (Jan-Jun). You can use any dataset suitable for you.

Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.

1. Calculate Upper and Lower Limits in Excel by Using MAX and MIN Functions

In this section, we are going to show you how to calculate upper and lower limits by using 2 built-in functions in Excel. They are MAX and MIN functions. No additional functions are needed.

Steps:

Firstly, go to cell J5 and type the following formula:

=MAX(C6:H15)

Secondly, press ENTER to get the Upper Limit result.

Again, go to cell K5 and type the following formula:

=MIN(C6:H15)

Next, press ENTER to get the Lower Limit result.

2. Apply a VBA Code to Calculate Upper and Lower Limits

At this point, you are going to learn to carry out the same task by writing a VBA code. According to this code, the maximum and minimum limits would be highlighted after running the code. A command button would also be generated which would help you to get the new upper and lower limits if some data in the dataset gets manipulated.

Steps:

First, go to the Developer tab in the title bar (If you have not used this tab before, it may not be inside the title bar at first. You have to add it manually. Here is the procedure of how to add the developer tab in the title bar).
Secondly, click on Insert. From the drop-down menu, under ActiveX Controls, select the rectangular box.

Then draw a rectangular box named CommandButton1.

Next, Click on the Design Mode tab and double-click on CommandButton1.

A new window will pop up and you can write your VBA code there.

VBA Code

Private Sub CommandButton1_Click()

Dim mycell As Range
Dim myrange As Range

Set myrange = Worksheets("VBA").Range("C6:H15")

lowerlimit = Application.WorksheetFunction.Min(myrange)
higherlimit = Application.WorksheetFunction.Max(myrange)

Worksheets("VBA").Range("C6:H15").Interior.ColorIndex = 0

For Each mycell In myrange

If mycell.Value = lowerlimit Then
mycell.Interior.ColorIndex = 4
End If

If mycell.Value = higherlimit Then
mycell.Interior.ColorIndex = 3
End If

Next mycell

End Sub

Code Mechanism

In the beginning, we declared two variables named “myrange” and “mycell”.
After that, we fixed the range of cells that is C6:H15, from which we need to find the maximum and minimum values.
Then, we set two more variables, lowerlimit and higherlimit to store the primary values of the maximum and minimum sales using Excel’s predefined MAX and MIN functions.
Then we added another line of code, to make all the boxes colorless before adding color to the new maximum and minimum cells after data manipulation.
Afterward, we created two For loops to go through the cells within the defined range sequentially and replace the stored data inside the lowerlimit and higherlimit variables, if the next cell data is lesser or bigger than the previously stored one. And with that, it fulfilled the purpose of the code.

After generating the code, click on the “play” button and run the code.

Return to the previous window of Excel and observe that it has highlighted the maximum and minimum limit values in the dataset.
If you want to change the name of the command button, go to the Developer tab, and click on Design Mode, Properties, and CommandButton1.
Rewrite the Caption according to your choice.

To test if your code works properly or not, manipulate some data on the dataset to set new upper and lower limits.
Then, click on the command button.

You can see that the new values that you have set just now, are highlighted instead of the previous values. This indicates that your code works just fine. (you can test the code in the same way, as many times as you want).

Things to Remember

You need to be very careful while writing the VBA code to avoid any type of logical or syntax error.
If you manipulate a dataset in order to test the code, make sure to undo the change after you are done.

Download Practice Workbook

Calculating Upper and Lower Limits.xlsm

Conclusion

Now, you know how to Calculate the Upper and Lower limits in a given dataset using Excel built-in functions or VBA code. Use them whenever you need and let us know if you have any better or additional way to carry out the task. You can drop any comment in the comment section below if you have any queries.

<< Go Back to Frequency Distribution in Excel | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!