This is the last of five posts in a series on how nonprofits can leverage Excel to simplify, streamline, and accelerate their data analysis. The IllumiLab is grateful to have one of our clients, Megan Ondr-Cooper of CASA St. Louis, sharing her enthusiasm and expertise with us. This time, Megan is talking about functions . . .
I’ve saved one of my favorite parts of Excel for last – functions. When I talk to people about their experiences with Excel, people talk about functions like its rocket science! However, everyone knows there must be a better way besides printing their spreadsheets and manually tallying, right?! Functions take the data you have and transform them into the data you need. For example, if your program prioritizes people on your waitlist by the number of risk factors, you can use a function to tell Excel, “If their number of risk factors is more than five, label them as High Risk.” Or if you are examining client drop-out rates, and you want to know how many clients completed more than five sessions, Excel can do that for you.
Frequently Used Functions
There are hundreds of different Excel functions. Below are a few of the functions I use most frequently, an example of a way you might use the function with your data, and a link to where you can learn more.
- Count – Count the number of cells in a range that contain numbers. You could use the count function to count how many participants in your dataset have a pre-test score.
- If – A logical function that returns one value if the condition is met and a different value if the condition is not met. I often use the =IF function to create a new variable based on the values of other cells. For example, if a program categorizes participation by phases (Phase 1 = <20 days, Phase 2 = 20+ days), you can use this function to create a new Phase variable that returns Phase 1 if the length of enrollment is <20 and Phase 2 is enrollment is 20+.
- Countif – Count the number of cells that meet your criteria. You could use this function to count the number of clients that showed improvement pre-test to post-test.
- Days – Returns the number of days between two dates. Use this function to calculate the length of enrollment or age from dates of birth (by dividing by 365).
For a comprehensive list of Excel functions, check out Exceljet’s 500 Excel Formula Examples.
Further Learning
A five-post blog series could never cover all there is to learn about Excel (I doubt 500 posts could even be sufficient!). Luckily there are many great resources you can use to continue your learning. A few of my favorites are:
If you want to take your learning to the next level, I would suggest learning to use the VLOOKUP function, Pivot Tables, or if you really want to get fancy, recording macros.
Farewell!
I hope this series introduced you to something new about Excel that will add speed and power to your data analysis. While I know many nonprofit professionals did not get into their work because they LOVE data, if you develop the skills to make meaning from your data, you can help your organization improve its impact. And that is something we can all get excited about!