Min and max

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:

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.

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 Dims are included, for extra things we need.

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.

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?

The first time through the loop, sCurrentRecord is 1. The weight for that record, in sWeight, is 12.

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:

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.

3.4028235E+38 is 34,028,235,000,000,000,000,000,000,000,000,000,000. The E means "times ten to the".

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.

I'd put everything into one loop. Like this:

A new pattern

Now we have a pattern for min/max. You can use it whenevs.

Exercise