As we leave one holiday season (Jewish holidaze, ya feel me??) and enter another (Pagan/Christian holiday season), comes the timeless riddle: when you regift something, what happens if that person regifts it, too? Do unwanted presents have a secret economy, making more exchanges than even Flat Stanley? Most paradoxically…. how would anyone even figure it out, since no one admits to regifting? Ahhh, what an enigma.
Aaaaanyway, it’s a spreadsheet quandary that made me think of this parable. I was working with an organization to determine their “retention rate” of Volunteers… a similar puzzle as figuring out returning CSA customers (which I did at a previous job) or “donor streaks” (popular topic in nonprofit fundraising database online fora!). What made this different? We decided to label each volunteer as “New” versus “Returning.” One thing led to another, and I started thinking of Chanukah gift shopping, hoping that my gifts don’t end up in someone else’s return pile, but also hoping even more that they don’t end up in a junk drawer!
Working through this request allowed me to write the most complex Excel formula that I’ve needed in quite some time! Which then inspired this blog post, which… If you give a mouse a cookie, and all that…
Here’s my work of formula art:
=IFS(H2=”No”, “did not volunteer this year”, AND(H2=”yes”, OR(D2=”yes”, C2=”yes”, F2=”Yes”)), “Returning”, AND(H2=”Yes”,F2=”no”, D2=”no”, C2=”No”), “New”)
Are you like…
Never fear! I’m going to walk through the steps and explain how I got to my solution!
Step 1: Develop your data question
In each of our regions, how many volunteers returned each year? How many volunteers were brand new?
Step 2: Draw a picture
My end result is going to look something like this! A table where the Rows are “Regions” and the Columns are “Years.” Within each year, we need numbers for New/Returning volunteers.
| Region | Year 1 | Year 2 | Year 3 |
| Venus | New: 20 | Returning: 5 New: 25 Total: 25 | Returning: 12 New: 8 Total: 20 |
| Jupiter | etc | etc | etc |
| Neptune | etc | etc | etc |
Step 3: Pull Reliable Data
Ahhh, this was fun. I pulled historic volunteer records from our database to see a list of volunteers in each year of the program. I ended up with a list of Volunteers from Year 1, a separate list of Volunteers from Year 2 (etc) and a list of All Time Volunteers. Each of these lists had a dedicated tab in a spreadsheet, with a good, descriptive name.
Step 4: Formulas!
There’s more than one way to solve the problem, but this way worked well enough for me, even if it isn’t the most elegant process ever!
In the “All Volunteers” tab, I created columns that represented each year of the program. The next thing I needed to do was determine if a particular volunteer participated. So for Volunteer #1 (we’ll call them Scooby Doo), we need “yes” for Year 1, “No” for Year 2, “Yes” for Year 3. Scooby took a year off while he was out solving mysteries. 🙂 The formula I used for this exercise was a VLOOKUP. The VLOOKUP’s job was to take Scooby’s name, and see if it showed up on the Year 1 tab. If Scooby volunteered in Year 1, the VLOOKUP us smart enough to return “Yes!”
At the end of this sub-step, I have a spreadsheet with Names, Years, and a lot of Yes/No’s. So, progress… but we’re still not there! A few more steps to go.
So, how do we take the “Yes/No” data and turn it into “New/Returning”? Ahhh my friend, this is where the IF (or IFS) formula(s) comes out to play. We’ll take a simple example first. We know Scooby volunteered in Year 1 and Year 3 of the program. In Year 1, Scooby was New. In Year 2, Scooby didn’t volunteer at all. In Year 3, Scooby was Returning. Each year, we need to compare Scooby’s attendance in all of the other years and see if he’s volunteered in the past. Here’s how I built my IF statement:
If…. Scooby volunteered in Year 3… and volunteered in Year 1 or Year 2 previously… say “Returning.” Otherwise, say “New.” If Scooby didn’t volunteer in Year 3 at all, say “Did not volunteer in Year 3”).
in formula speak, this is how you would do it…
=IFS(Year3=”No”, “did not volunteer in Year 3″, AND(Year3=”yes”, OR(Year2=”yes”, Year1=”yes”)), “Returning”, AND(Year3=”Yes”,Year2=”no”, Year1=”no”), “New”)
Woohoo! Now we’re really onto something! We know who was new and who was returning each year. But we haven’t summarized by region yet.
Good thing I can use a simple Pivot Table to re-arrange my data! (Important caveat: My All Volunteers list included what region the volunteer was affiliated with! Without that data point, this would be ~impossible~!)
Here’s a .GIF that shows Pivot Tables in action. The goal here is to show you what could be possible, rather than break down the exact steps I took. Pivot Tables are a nifty way to summarize/splice/dice/aggregate data into useful categories. They are very powerful! Better yet, they are available in Excel and Google Sheets!
I created one pivot table for each year of my data and summarized New versus Returning volunteers, grouped by region (similar to the GIF above).
And just like that, I had my answer! I could further take the data and find rates, ratios, or change over time… or calculate other nifty formulas like they do here (warning! business-y language, but potentially interesting ideas for nonprofits interested in retaining donors and volunteers!) After doing all of this data manipulation, I kinda felt like, well, I could do aaaaanything!
What did I actually do? Made a few phone calls… ate some wonton soup for lunch… and spent the rest of the day re-arranging Mail Merge templates. Riveting, I know!
These types of requests are complex, but they aren’t insurmountable. It’s ok if you feel overwhelmed! The best thing to do is NOT rush to cram all of the formulas, but rather, ask a nice, juicy data question and then work up to the skills you need to answer it – even if you need some coaching along the way. Many systems like this can be automated if you know you will need to re-evaluate the metric periodically.
Expand your horizons! Ask hard questions! I’ll be here to catch you if you get stuck <3
So useful and clear.