By Dr. Greg Duke
During our Water Cooler Chat, I presented some powerful Excel techniques— Watch the replay video for tips on:
Identify Top Donors Using =RANK.EQ()
=RANK.EQ() (and its counterpart =RANK.AVG(), where equally-ranked items are given an average rank instead of their lowest possible rank), is a good way to identify top donors (or top volunteers).
As an example, if total donations from each donor are in column B, use the formula =RANK.EQ(b2, $b$2:$b$[end of list],0) to find where each prospect would be if the worksheet was sorted by total donations with the largest figure at the top. You don’t have to sort the whole list: You can just rank it instead.
Rank for Top of the Class Using =RANK>EQ()
What if we want to know who are our top donors by both total giving and number of gifts? Use =RANK.EQ() to identify “combined ranks”. To do that:
Finding Hidden Gems with =SUMIFS(),-=COUNTIFS(), and =AVERAGEIFS()
Not only are the functions =SUMIFS(), =COUNTIFS(), and =AVERAGEIFS() helpful in finding the total, number of, and average giving to certain funds or campaigns (consider how many planned gifts you can just pull out of a list of hundreds of thousands of gifts), but also powerful tools for building a dashboard.
You can build your dashboard from a source file consisting of a list of gifts exported from your database. In our example below, the gift amount is in column B, the fund designation for each gift is in column C, and the donor’s state is in column D, listed in lines 2 through 1000. Let’s break this formula into its constituent parts:
Key Tip: For each function to work, the criteria range(s) and the sum or average range must start on the same line and end on the same line.
Think about using these functions on your detail worksheets in order to summarize data for a dashboard.
To find out more about the powerful uses of the tool that your office already has, find my other courses on the Staupell Website, here: https://www.staupell.com/business-intelligence-visualization-reporting.html
Feel free to write me at email@example.com with your ideas and questions.