Accumulators

Totaling

Check out this worksheet:

Data

Here's code for the button:

  1. Private Sub cmdRun_Click()
  2.     Dim total As Single
  3.     Dim row As Integer
  4.     total = 0
  5.     For row = 1 To 5
  6.         total = total + Cells(row, 1)
  7.     Next row
  8.     Cells(8, 2) = total
  9. End Sub
total is initialized to zero. Then the loop starts. The CPU runs line 6 again and again. The first time, row is 1. Then 2, and so on, up to 5. Line 6 grabs Cells(row, 1) and adds it to total. The first time through the loop, row is 1, so Cells(1, 1) is added to total. The next time through the loop, Cells(2, 1) is added, and so on, up to Cells(5, 1).

Totaling

total is an accumulator. It's a variable that tracks something about a data set during a loop.

Accumulators should always be initialized. In this program, that's in line 4:

  1. Private Sub cmdRun_Click()
  2.     Dim total As Single
  3.     Dim row As Integer
  4.     total = 0
  5.     For row = 1 To 5
  6.         total = total + Cells(row, 1)
  7.     Next row
  8.     Cells(8, 2) = total
  9. End Sub

total is added to each time through the loop, with a different cell.

  1. Private Sub cmdRun_Click()
  2.     Dim total As Single
  3.     Dim row As Integer
  4.     total = 0
  5.     For row = 1 To 5
  6.         total = total + Cells(row, 1)
  7.     Next row
  8.     Cells(8, 2) = total
  9. End Sub
At the end of the loop, total describes something about the entire data set.

Pattern

Accumulator

Situation:
You want to summarize some aspect of a data set, like the average, or highest value.
Actions:
Use a variable as an accumulator.

  • Initialize the variable.
  • Loop over the cells you want to process. Update the value of the accumulator each time through the loop.
  • After the loop, the accumulator has the value you want.

Don't rely on the value of the index variable (row in the example) after a For-Next loop. It's predictable in some cases, but not in others.

Totals that aren't

In the example, total is the simple sum of the cells:

total = total + Cells(row, 1)

But it doesn't have to be. For example, in statistics, you sometimes want the sum of squares. That is, take each value, square it, and add those up. It's used in, for example, calculating standard deviations.

This line would compute the sum of squares:

sumSq = sumSq + Cells(row, 1) * Cells(row, 1)

You can do both calculations at once:

  1. Private Sub cmdRun_Click()
  2.     Dim total As Single
  3.     dim sumSq as Single
  4.     Dim row As Integer
  5.     total = 0
  6.     sumSq = 0
  7.     For row = 1 To 5
  8.         total = total + Cells(row, 1)
  9.         sumSq = sumSq + Cells(row, 1) * Cells(row, 1)
  10.     Next row
  11.     Cells(8, 2) = total
  12.     Cells(8, 3) = sumSq
  13. End Sub

Jeremy
Jeremy
You've got total and sum. I know they mean the same thing, but would it be better to be consistent?
Tara
Tara
You're right, Jeremy. Let's change it.

  1. Private Sub cmdRun_Click()
  2.     Dim sum As Single
  3.     dim sumSq as Single
  4.     Dim row As Integer
  5.     sum = 0
  6.     sumSq = 0
  7.     For row = 1 To 5
  8.         sum = sum + Cells(row, 1)
  9.         sumSq = sumSq + Cells(row, 1) * Cells(row, 1)
  10.     Next row
  11.     Cells(8, 2) = sum
  12.     Cells(8, 3) = sumSq
  13. End Sub

Tara
Tara
Just a detail, but it makes the code easier to read.

Highest

Accumulators aren't just for totals. You can use them for all sorts of things, like finding the highest value. Here's that worksheet again:

Data

Here's the code:

  1. Private Sub cmdRun_Click()
  2.     Dim highest As Single
  3.     Dim row As Integer
  4.     highest = Cells(1, 1)
  5.     For row = 2 To 5
  6.         If Cells(row, 1) > highest Then
  7.             highest = Cells(row, 1)
  8.         End If
  9.     Next row
  10.     Cells(8, 2) = highest
  11. End Sub
highest is an accumulator. As the loop runs, it holds the highest value that the program has found so far. At the end of the loop, it holds the highest value in the entire data set.

The initialization is different:

  1. Private Sub cmdRun_Click()
  2.     Dim highest As Single
  3.     Dim row As Integer
  4.     highest = Cells(1, 1)
  5.     For row = 2 To 5
  6.         If Cells(row, 1) > highest Then
  7.             highest = Cells(row, 1)
  8.         End If
  9.     Next row
  10.     Cells(8, 2) = highest
  11. End Sub
Rather than initializing to 0, the program grabs the first data value for highest. Then it loops through the rest of the data, comparing each element with highest.

Cell with the highest

Suppose that we didn't want to know the highest number, but the cell that contained the highest number. So for this…

Data

… it's not 92 we want, it's 4. That's the row with the highest number in it.

Highest cell

Easy peasy.

  1. Private Sub cmdRun_Click()
  2.     Dim highestCell As Integer
  3.     Dim row As Integer
  4.     highestCell = 1
  5.     For row = 2 To 5
  6.         If Cells(row, 1) > Cells(highestCell, 1) Then
  7.             highestCell = row
  8.         End If
  9.     Next row
  10.     Cells(8, 2) = highestCell
  11. End Sub
Instead of using an accumulator to track the highest value, we use an accumulator to track the cell with the highest value.

Lowest

Almost the same as the highest code. Let's add to the code we have.

  1. Private Sub cmdRun_Click()
  2.     Dim highest As Single
  3.     Dim lowest As Single
  4.     Dim row As Integer
  5.     highest = Cells(1, 1)
  6.     lowest = Cells(1, 1)
  7.     For row = 2 To 5
  8.         If Cells(row, 1) > highest Then
  9.             highest = Cells(row, 1)
  10.         End If
  11.         If Cells(row, 1) < lowest Then
  12.             lowest = Cells(row, 1)
  13.         End If
  14.     Next row
  15.     Cells(8, 2) = highest
  16.     Cells(9, 2) = lowest
  17. End Sub

Exercises

Exercise: Total-high-low
Write a program to find the total, highest value, and lowest value of a data set. Download the starting worksheet. Add code that outputs:

Output

Upload your workbook.

Attachments

total-high-low-start.xlsm

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Exercise: Stats for price data
Download this workbook. It has data on prices for a product. It looks like this:

Start

The number of data items is given in cell B7.

Add code that computes the average, highest value, and lowest value of the data set. Make sure the program works when the amount of data changes.

Validate n, the number of data items. If the user enters a value less than 2, show this:

Error

Upload your file.

Attachments

average-prices-start.xlsm

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Summary

An accumulator is a variable that tracks something about a data set during a loop. Accumulators should always be initialized.