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
Else
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
Else
freqScore = -1
End If
End Function