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.

**Read More:** How to Create a Grouped Frequency Distribution in Excel

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

**Read More: **How to Make a Relative Frequency Histogram in Excel

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

**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.

**Related Articles**

- How to Make Frequency Distribution Table in Excel
- How to Find Mean of Frequency Distribution in Excel
- How to Do a Frequency Distribution on Excel
- How to Calculate Relative Frequency Distribution in Excel
- How to Calculate Cumulative Relative Frequency in Excel
- How to Calculate Cumulative Frequency Percentage in Excel

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