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