A Custom Excel Function To Calculate RF(M) Scores For Fundraisers

To accompany a guest post with Gravyty, I want to share an Excel function we wrote to take your entire constituency and generate a Recency-Frequency score for each individual.

This function is like other built-in functions you may have used in Excel, except that we’ve programmed it to do exactly what we want: determine the Recency-Frequency score for each individual donor record.

Recency-Frequency Score = Recency Score + Frequency Score

  • Recency-score function requires the last gift date
  • Frequency-score function requires a person’s year of graduation and how many years they have contributed

Then, just add these two numbers together.

It might sound complicated, but once you get through it it’s fairly simple. Once you get the hang of it, you’ll be able to run these any time you wish. For more information, check out these links from Microsoft’s Office support pages:

  • Create formulas in Excel
  • Making calculations using functions in Excel
  • Create custom functions in Excel

I am happy to help where I can. Whether you need other custom functions or better understanding how these may benefit your organization, let me know.

Excel Function: recencyScore

Public Function recencyScore(r As Range) As Integer

    Dim lastdonate As Date

    Dim donatefiscal As Integer

    Dim currentfiscal As Integer

    Dim d As Integer

    lastdonate = CDate(r.Value)

    ‘ we assume fiscal years run from July to June. If that changes,

    ‘ this next line will need to be modified

    donatefiscal = IIf(Month(lastdonate) <= 6, Year(lastdonate) – 1, Year(lastdonate))

    currentfiscal = IIf(Month(Now()) <= 6, Year(Now()) – 1, Year(Now()))

    d = currentfiscal – donatefiscal

    ‘Has never made a gift

    If Not IsDate(r.Value) Then

        recencyScore = 0

    ‘Has made an FY15 gift

    ElseIf d = 0 Then

        recencyScore = 20

    ‘Gave last year

    ElseIf d = 1 Then

        recencyScore = 20

    ‘Gave 2 year ago

    ElseIf d = 2 Then

        recencyScore = 15

    ‘Gave 3 years ago

    ElseIf d = 3 Then

        recencyScore = 10

    ‘Gave 4 years ago

    ElseIf d = 4 Then

        recencyScore = 5

    ‘Gave 5 years ago

    ElseIf d = 5 Then

        recencyScore = 2

    ‘Gave more than 5 years ago

    ElseIf d > 5 Then

        recencyScore = 1

    ‘Error score


        recencyScore = -1

    End If

End Function

Excel Function: freqScore

Public Function freqScore(giftCount As Integer, classYear As Integer) As Integer

    Dim freqP As Double

    ‘Calculate the frequency ratio

    ‘# years a gift has been made divided by the total # years possible

    freqP = giftCount / (2014 – classYear)

    ‘Check to see if they have ever made a gift

    ‘If no gifts, then it’s zero

    ‘otherwise, continue

    If giftCount = 0 Then

        freqScore = 0

    ElseIf freqP >= 1 Then

        freqScore = 30

    ElseIf freqP >= 0.9 And freqP < 1 Then

        freqScore = 24

    ElseIf freqP >= 0.8 And freqP < 0.9 Then

        freqScore = 18

    ElseIf freqP >= 0.7 And freqP < 0.8 Then

        freqScore = 12

    ElseIf freqP >= 0.6 And freqP < 0.7 Then

        freqScore = 6

    ElseIf freqP < 0.6 Then

        freqScore = 3

    ‘Error score


        freqScore = -1

    End If

End Function



On Becoming Obsolete

Technology comes to mind. That computer which won’t boot up in less than 10-seconds. That car without integrated Bluetooth. 

What about humans?

Last week — during a conference in Indianapolis I attended — someone remarked over dinner how these old men were complaining about the millennials. And how out of touch “they” seemed. How old and curmudgeonly. Fair enough. They were. 

I then asked if he wasn’t giving them the exact same treatment. Particularly when millennials look at the oldest generation as being from another time and place so as to make them irrelevant (in some respects). Were we not giving them the exact same treatment? What makes them any different and will we know when we ourselves have become obsolete?

Can we avoid obsolescence? A computer can be upgraded. A car can be retrofitted. But sometimes it isn’t deemed worthwhile. Is there an analogous workaround for us? Where technology upgrades usually connotes hardware improvements, aren’t we searching for a mental enrichment? I suppose this would need to be the case, otherwise obsolescence would be inherent with age and there’d be nothing for us to do but to force fit a coexistence. 

Obsolescence may not be the most appropriate word. Defined as “out of date and no longer used”, it seems inaccurate that this should describe someone who is reluctant to change, who ceases to educate themselves in order to remain contemporaries, if not in age at least in mindset and mindfulness. Which word then describes this phenomenon?

I do hope to avoid this myself — I write this knowing full well it’s inevitable to a small extent. Moments when I share the distance I keep with Facebook or my skepticism with Snapchat and other so-called social media apps I feel it setting in. For myself I realize it’s enough to know and understand. To appreciate. Avoiding “obsolescence” does not require I adopt everything; just adapt.

Perhaps it’s important to reframe this from the avoidance of “obsolescence” to that of maintaining relevance. In that way, we can find belonging in an ever changing society.

What You Measure Is What You Get

If you are looking to improve year-over-year, it’s a good idea to know what that improvement looks like.

Typically, we go straight for total dollars raised or aggregate participation rate. But in most situations we aren’t expecting huge gains, instead we are looking for many smaller wins to yield that 2-3% increase to keep us ahead of last year’s trend line.

Donor acquisition is costly. Looking at increased participation to drive your increase in dollars misses a larger opportunity.

Focusing on recapturing donors is nothing new, but what metrics are you looking at to track this?

Lastly, fundraising efforts rarely rely on a single contributor to be successful. Fundraising is a team sport. But in meeting I’ve been privy to, there is a disproportionate amount of time spent taking about aggregate metrics as opposed to incremental program-based metrics where individual contributors can — and feel as though they — make a difference.

A Tiny “Primer” On Basic Stats

You might know your average gift size from last year, but are you actively tracking this year? More importantly, why are you asking for the average size? Averaging can quickly become irrelevant when you look at your giving data. What does it mean to know that your average gift is $587? (Not much in my estimation.)

Conversely, by looking at the median — or the 50th-percentile — you get a better way to gauge your performance. A median gift of $100 tells a more meaningful story. It indicates that half of your donors are contributing at or below $100. And you can bet you have many people who are giving this amount. (But don’t take my word for it, sort and scroll through your raw data or a frequency table.)

And if by “average” you are really wondering what the most common gift is, look no further than the mode.

Another important distinction — mean vs. median — is that often the mean value is not an actual gift size, but you can be sure the median value is. Rather, the mean is simply showing the central tendency of your giving data.

Data For The Sake of Data

The mistake most people make when it comes to data is misunderstanding what it promises. Data is not a means to an end, rather, it is instrumental in answering strategic questions.

Grasping the different applications of these basic stats will enable you to better articulate your questions and take advantage of your data to answer those questions. You’ll be closer to making data-driven decisions.

Armed with this knowledge, you can ask yourself:

  • What are my objectives this year? What metrics do I need to track?
  • Are my current tactics focused on the right areas?
  • Am I measuring our results in a meaningful way?
  • Are my efforts having the affect I intended?
  • What results do I need in order to declare an effort successful?

What happens to your fund if you raise your median gift size from $100 to $125?

What would it take to do this?

Hint: Stronger data-driven segmentation, dynamic gift ask amounts, stewardship tied to the right levels.


This post originally appeared on Gravyty.com.

An Indirect Measure On Computer Sales

It’s official. I have seen it too many times now. People don’t know how to type on a traditional, physical American QWERY keyboard. Too often I’ve now seen people puzzled, looking for their familiar iOS mobile keyboard “where is the at-sign?” (answer Shift-2) or even tapping on a regular computer display and wondering why it isn’t working.

If sales data for desktop computers isn’t enough to convince you of their declining dominance, maybe the growing expectations set by our mobile devices might.