Finding Christmas tree entries in survey data

[kualjo]

Does anyone have any experience with formulas or macros that can help determine individual survey response sets that might be invalid because the respondent christmas-treed the survey. I can find straight-lined responses, but christmas-trees are trickier. Because of the nature of the survey, this actually shouldn't be a problem, but I want to be sure that I have the cleanest data possible.

The survey is 75 questions with 6 response items each (Strongly disagree to Strongly agree). There are reverse-scored items, so an average sample set would tend to spread equally around the midpoint.

 

[mjbeam]

What would you consider a Christmas tree response to the survey. In other words, what pattern exactly are you trying to detect?

 

[mjbeam]

I played around with some data and found a possible way to detect systematic vs random sequences in the surveys. If you do a discrete cosine transform on the data and look at the range of the frequency data you can spot systematic variance in the data. Here is a UDF that will return the range of the frequency space after the transform:

Public Function DCT_Range() As Double

    Dim dct(75), tMax, tMin As Double
    
    tMin = 256
    tMax = -256
    For i = 1 To 74
        t = 0
        For x = 0 To 74
            t = t + Cells(x + 2, 2) * Cos((Application.WorksheetFunction.Pi() * _
                (2 * x + 1) * i) / (2 * 75))
        Next
        t = (2 / 75) ^ 0.5 * t
        If tMax < t Then tMax = t
        If tMin > t Then tMin = t
    Next i
    
    DCT_Range = tMax - tMin
    
End Function

This UDF assumes 76 rows of data (headers in row 1, values in rows 2-76), question number in column A and answer (1-6) in column B. I populated column B with random data and the UDF returned a range of about 5. I then entered repeating data such as (1,6,1,6,1,6,1,6...) and (1,2,3,4,5,6,5,4,3,2,1,2,3...) and (1,2,3,4,5,6,1,2,3,4,5,6,1,2,3...) and the UDF returned ranges from 9 to 13.