Cutie!
Photo: Holly Vickery
Often you want to find the smallest (minimum) and largest (maximum) values in a data set. For example, the goat with the smallest weight, and the goat with the largest weight. This lesson is about how you do that.
We'll use this small data set...
... but the method will work for a data set of any size.
Heaviest goat
Let's start by finding the heaviest goat. Assume for now there are no data errors. You already know how to do validation on a Range
.
Here's the worksheet we'll use.
Let's write code to fill in the record count, and heaviest goat cells.
Here's the general approach:
- Set up the range
- For each record
- Get weight for current record
- If weight > highest weight so far:
- highest weight so far = weight
So, we make a new variable, to hold the highest weight. Work through the records. For each one, compare the weight in the record with the highest one we've found. If the current record's weight is greater, we have a new highest weight.
Let's make the Range
and the loop.
- 'Set up the Range.
- Set rGoatData = Range(Cells(18, 1), Cells(18, 1).End(xlToRight).End(xlDown))
- sNumberOfRecords = rGoatData.Rows.Count
- 'Loop over records.
- For sCurrentRecordNumber = 1 To sNumberOfRecords
- tName = rGoatData.Cells(sCurrentRecordNumber, 1)
- sWeight = rGoatData.Cells(sCurrentRecordNumber, 2)
- CODE TO FIND HEAVIEST
- Next sCurrentRecordNumber
- 'Output
- Cells(6, 2) = sNumberOfRecords
- Cells(10, 2) = tNameHeaviest
- Cells(10, 3) = sMaximumWeight
Check out the last two lines. To output the name of the heaviest goat, we'll need a variable with the name in it. So that's one thing our processing code will have to find.
To output the heaviest weight, we'll need a variable for that. The processing code will work out the variable's value.
Here's code that will do it. Some Dim
s are included, for extra things we need.
- Dim sMaximumWeight As Single
- Dim tNameHeaviest As String
- ...
- 'Set up the Range.
- Set rGoatData = Range(Cells(18, 1), Cells(18, 1).End(xlToRight).End(xlDown))
- sNumberOfRecords = rGoatData.Rows.Count
- 'Loop over records.
- For sCurrentRecordNumber = 1 To sNumberOfRecords
- tName = rGoatData.Cells(sCurrentRecordNumber, 1)
- sWeight = rGoatData.Cells(sCurrentRecordNumber, 2)
- 'Test for max.
- If sWeight > sMaximumWeight Then
- sMaximumWeight = sWeight
- tNameHeaviest = tName
- End If
- Next sCurrentRecordNumber
- 'Output
- Cells(6, 2) = sNumberOfRecords
- Cells(10, 2) = tNameHeaviest
- Cells(10, 3) = sMaximumWeight
Go through the records, one at a time. sMaximumWeight
holds the maximum weight we've found so far.
Each time we grab a record, compare its weight to the sMaximumWeight
. If the record's weight is higher, remember the new maximum, and the name of that goat.
When we get to the end of the data set, sMaximumWeight
will have the highest weight, and tNameHeaviest
will have the name of that goat.
Let's give it a try.
Yay! It worked!
Lightest goat
Let's do the same thing for the lightest goat.
- Dim sMaximumWeight As Single
- Dim tNameHeaviest As String
- Dim sMinimumWeight As Single
- Dim tNameLightest As String
- ...
- 'Set up the Range.
- Set rGoatData = Range(Cells(18, 1), Cells(18, 1).End(xlToRight).End(xlDown))
- sNumberOfRecords = rGoatData.Rows.Count
- 'Loop over records.
- For sCurrentRecordNumber = 1 To sNumberOfRecords
- tName = rGoatData.Cells(sCurrentRecordNumber, 1)
- sWeight = rGoatData.Cells(sCurrentRecordNumber, 2)
- 'Test for max.
- If sWeight > sMaximumWeight Then
- sMaximumWeight = sWeight
- tNameHeaviest = tName
- End If
- 'Test for min.
- If sWeight < sMinimumWeight Then
- sMinimumWeight = sWeight
- tNameLightest = tName
- End If
- Next sCurrentRecordNumber
- 'Output
- Cells(6, 2) = sNumberOfRecords
- Cells(10, 2) = tNameHeaviest
- Cells(10, 3) = sMaximumWeight
- Cells(14, 2) = tNameLightest
- Cells(14, 3) = sMinimumWeight
Same code, but with a less than in the If
, rather than a greater than.
Let's try it.
Wait, what!? The lightest goat is weightless?
That doesn't look right.
The lightest is supposed to be 12.
Hey, Georgina. Can you work out the problem?
![Georgina](https://vba.skilling.us/sites/default/files/skilling/characters/georgina-pc2.jpg)
Georgina
I'll try. Let's see... I'll add a breakpoint in the processing loop. Let's see what happens.
The first time through the loop, sCurrentRecord
is 1. The weight for that record, in sWeight
, is 12.
![Georgina](https://vba.skilling.us/sites/default/files/skilling/characters/georgina-pc1.jpg)
Georgina
OK, I see the problem. The If
is comparing the goat weight (that's 12) with zero.
- If sWeight 12 < sMinimumWeight 0 Then
All of the weights are more than zero, so sMinimumWeight
is never changed.
![Ray](https://vba.skilling.us/sites/default/files/skilling/characters/ray-listening1.jpg)
Ray
Why is sMinimumWeight
zero?
Wait..., never mind. It starts out at zero. That's what all variables start out as. Well, all Single
s, anyway.
Right. The problem is that sMinimumWeight
starts out at 0, which is smaller than the actual smallest value in the data, so it never gets changed by the If
statement.
To fix it, let's initialize sMinimumWeight
to a very large value. We know no goats weight a million kilos, so we could do something like this:
- sMinimumWeight = 1000000
- 'Loop over records.
- For sCurrentRecordNumber = 1 To sNumberOfRecords
- tName = rGoatData.Cells(sCurrentRecordNumber, 1)
- sWeight = rGoatData.Cells(sCurrentRecordNumber, 2)
- ...
- 'Test for min.
- If sWeight < sMinimumWeight Then
- sMinimumWeight = sWeight
- tNameLightest = tName
- End If
- Next sCurrentRecordNumber
All of the records will have weights less than 1,000,000 kilos. The first time through the loop, the weight in the first record will be less than 1,000,000, so it will become the new minimum at that point.
So we should get the smallest. Let's try it.
Yay!
We can do a little better. Instead of 1,000,000, we could set sMinimumWeight
to the largest number VBA can put in a Single
.
- Dim sMinimumWeight as Single
- ...
- sMinimumWeight = 3.4028235E+38
- For sCurrentRecord = 1 To sNumberRecords
3.4028235E+38
is 34,028,235,000,000,000,000,000,000,000,000,000,000. The E
means "times ten to the".
![Adela](https://vba.skilling.us/sites/default/files/skilling/characters/adela-pc1.jpg)
Adela
Should we make the same change to the maximum code? Just in case.
Oh, right! Yes, we should. sMaximumWeight
starts off at zero, and all of the goat weights will be more than that.
But if we were dealing with, say, temperature data for different places, it's possible that all of the temperatures would be negative. Then we'd get zero for the maximum, rather than the right value.
So let's initialize sMaximumWeight
to the smallest possible value. Every real value will be bigger than that.
- Dim sMaximumWeight as Single
- ...
- sMaximumWeight = -3.4028235E+38
- For sCurrentRecord = 1 To sNumberRecords
![Ethan](https://vba.skilling.us/sites/default/files/skilling/characters/ethan-smile7.png)
Ethan
OK, we want the lightest goat, and the heaviest goat. Would you have two separate loops, or put everything into one loop?
I'd put everything into one loop. Like this:
- 'Set up the Range.
- Set rGoatData = Range(Cells(18, 1), Cells(18, 1).End(xlToRight).End(xlDown))
- sNumberOfRecords = rGoatData.Rows.Count
- 'Initialize min weight to highest number possible for a Single.
- sMinimumWeight = 3.4028235E+38
- 'Initialize max weight to most negative value possible for a Single.
- sMaximumWeight = -3.4028235E+38
- 'Loop over records.
- For sCurrentRecordNumber = 1 To sNumberOfRecords
- tName = rGoatData.Cells(sCurrentRecordNumber, 1)
- sWeight = rGoatData.Cells(sCurrentRecordNumber, 2)
- 'Test for max.
- If sWeight > sMaximumWeight Then
- sMaximumWeight = sWeight
- tNameHeaviest = tName
- End If
- 'Test for min.
- If sWeight < sMinimumWeight Then
- sMinimumWeight = sWeight
- tNameLightest = tName
- End If
- Next sCurrentRecordNumber
- 'Output
- Cells(6, 2) = sNumberOfRecords
- Cells(10, 2) = tNameHeaviest
- Cells(10, 3) = sMaximumWeight
- Cells(14, 2) = tNameLightest
- Cells(14, 3) = sMinimumWeight
A new pattern
Now we have a pattern for min/max. You can use it whenevs.
Loop across some records. Put an If
in the loop, comparing the current value with the smallest/largest so far.
Exercise
Dancing with the goats
Download the starting workbook. It has data on scores from an episode of the new hit TV show, "Dancing with the Goats."
It starts like this:
Click the button, and see the goats with the highest and lowest scores.
Write the code so it would still work if the number of records changed.
Upload your workbook. The usual standard apply.