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.
Download Practice Workbook
You can download and practice the dataset we used to prepare this article.
2 Methods to Calculate Upper and Lower Limits in Excel
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. 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: Excel MIN and MAX in Same Formula (2 Practical Examples)
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 get 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 SubCode 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 Find Lowest Value with Criteria in Excel (7 Effective Ways)
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.
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 Set Row Height Limit in Excel (3 Easy Methods)
- How to Find Minimum Value in Excel (6 Easy Ways)
- Calculate Upper Control Limit with Formula in Excel
- Use ADDRESS Function in Excel (7 Suitable Examples)
- How to Increase Character Limit in Excel Cell (2 Easy Methods)
- Limit Scroll Area in Excel (3 Easy Ways)
- Determine Sheet Name Limit in Excel (with Easy Steps)
