Compare 2021 Volunteer Service to 2019

Posted By: Volgistics TeamPosted On:

Many organizations like to print year-end or quarterly reports showing how volunteer service numbers have changed from one year to the next. However, many organizations also consider 2020 to be too much of an anomaly, and prefer to compare this year to 2019 instead.

To get this information, we recommend printing a service summary report in Excel format, then use the features in Excel to get the comparisons. This requires a little advanced work in Excel, but don’t worry–we’ll walk you through it. We’ll create a custom report as an example. To get started:

  1. Choose Print from the menu
  2. Expand Service summary.
  3. Click Create a new service summary report.
  4. On the Options tab, choose how you’d like the report organized and summarized, and whether you’d like to list volunteers.
  5. On the Fields tab, check the values you’d like. Be sure to check Prior year comparisons.
  6. On the Include tab, choose to print the report in Excel format and select the years you would like. Be sure that 2019 is included in the range.
  7. On the Save tab, enter a name for this custom report
  8. Click Save and Print.
  9. Click Start printing and the report will appear in your Mailbox shortly.

Now that you’ve got the report, let’s do some work in Excel:

  1. Open the report in Excel.
  2. You should see a series of columns for 2019 Hours, 2020 Hours, Change, 2020 Hours, 2021 Hours, Change.
  3. From this series, select the columns 2020 Hours, Change, and 2020 Hours
  4. Right-click on the selected area and choose Delete to remove those columns entirely.

    In our example, 2019 Hours, 2021 Hours, and Change are now columns G, H, and I respectively. If your columns don’t line up exactly, you’ll need to adjust the following steps accordingly.
  5. Highlight the Change column that appears after 2021 Hours (column I) and press DEL on your keyboard. This will clear the contents of the column without removing the column itself.
  6. In I1, type a heading for the column, e.g. Change
  7. In I2, enter this formula: 
=IF(AND(G2<>"",G2>0),(H2-G2)/G2,"")
  1. Press ENTER on your keyboard, and the click cell I2 again.
  2. Scroll to the bottom of the report, hold the SHIFT key and click the last cell in column I. This will highlight the whole range
  3. Press CTRL+D on your keyboard to fill down through the whole selection.

Now that the formula is inserted, click the Percent button in the ribbon to change the formatting for this column. You can also use a custom format if you prefer.

BONUS: How does that formula work?

Let’s start from the base formula and build it up from there. To calculate percent change, you take the difference between two values and divide it by the second value. In this case, it looks like

=(H2-G2)/G2

If you fill down with this formula, you’ll quickly see the issue– if G2 is blank, you’ll get an error. To account for this, we wrapped the formula in an IF statement:

=IF(G2<>””,(H2-G2)/G2,””)

This way, if G2 is blank, we’ll get a blank cell instead of an error. As before, if you fill down with this formula, you’ll see that there’s still a problem, because a different error will occur if G2 is 0. So we use the AND function to add another condition to the IF function, like this:

=IF(AND(G2<>””,G2>0),(H2-G2)/G2,””)

Don’t like YouTube? Click here instead.