CODEWITHSUNDEEP
excelvbaaverage
shecodes
shecodes

Reputation: 111

AverageIf function results in "#VALUE!"

This is a screenshot of my data.data screenshot

Dim dBT As Object 'global dictionary

Sub buttonpresscount()

    'constants for column positions
    Const COL_BLOCK As Long = 1
    Const COL_TRIAL As Long = 2
    Const COL_ACT As Long = 7
    Const COL_AOI As Long = 8
    Const COL_RT As Long = 16
    Const COL_FT As Long = 17

    Dim rng As Range, lastrow As Long, sht As Worksheet
    Dim d, r As Long, k, resBT()

    Set sht = Worksheets("full test")
    lastrow = sht.Cells(Rows.Count, 3).End(xlUp).Row
    Set dBT = CreateObject("scripting.dictionary")

    Set rng = sht.Range("B7:T" & lastrow)

    d = rng.Value  'get the data into an array

    ReDim resBT(1 To UBound(d), 1 To 1) 'resize the array which will
                                        '  be placed in ColT

    'get unique combinations of Block and Trial and pressedcounts for each
    For r = 1 To UBound(d, 1)
        k = d(r, COL_BLOCK) & "|" & d(r, COL_TRIAL) 'create key
        dBT(k) = dBT(k) + IIf(d(r, COL_ACT) <> "", 1, 0)
    Next r

    'populate array with appropriate counts for each row
    For r = 1 To UBound(d, 1)
        k = d(r, 1) & "|" & d(r, 2)   'create key
        resBT(r, 1) = dBT(k)         'get the count
    Next r

    'place array to sheet
    sht.Range("T7").Resize(UBound(resBT, 1), 1) = resBT

    'clear dictionary
    dBT.RemoveAll

'count AOI entries
 For r = 1 To UBound(d, 1)
        k = d(r, COL_BLOCK) & "|" & d(r, COL_TRIAL) 'create key
        If resBT(r, 1) = 1 Then    'only proceed with trials with 1 button press
        dBT(k) = dBT(k) + IIf(d(r, COL_AOI) = "AOI Entry", 1, 0)    'get count
        Else: dBT(k) = ""
        End If
    Next r

    'populate array with appropriate counts for each row
    For r = 1 To UBound(d, 1)
        k = d(r, 1) & "|" & d(r, 2)   'create key
        resBT(r, 1) = dBT(k)          'get the count
    Next r

    'place array to sheet
    sht.Range("U7").Resize(UBound(resBT, 1), 1) = resBT

Call createsummarytable
Call PopSummaryAOI(dBT)

dBT.RemoveAll

'retrieve and print reaction times to data summary sheet
   For r = 1 To UBound(d, 1)
        If resBT(r, 1) <> "" Then 'if buttonpresscount = 1 and AOI count exists
        k = d(r, COL_BLOCK) & "|" & d(r, COL_TRIAL) 'create key
        dBT(k) = d(r, COL_RT)
        End If
    Next r

 'Populate array with last row reaction time for each trial
    For r = 1 To UBound(d, 1)
        k = d(r, 1) & "|" & d(r, 2)   'create key
        resBT(r, 1) = dBT(k)          'get the count
    Next r

Call PopSummaryRT(dBT)

dBT.RemoveAll

'work out avg fixation time per trial
For r = 1 To UBound(d, 1)
    If resBT(r, 1) <> "" Then
    k = d(r, COL_BLOCK) & "|" & d(r, COL_TRIAL) 'create key
    dBT(k) = Application.AverageIf(d(r, COL_FT), (d(r, COL_AOI) = "AOI Entry"))
    End If
Next r

'populate array
For r = 1 To UBound(d, 1)
        k = d(r, 1) & "|" & d(r, 2)   'create key
        resBT(r, 1) = dBT(k)          'get the count
Next r

Call PopSummaryFT(dBT)

End Sub

Referring to the above macro, the following lines of code are meant to work out an average of values in Column R per dict(key) (read: per trial):

For r = 1 To UBound(d, 1)
    If resBT(r, 1) <> "" Then
    k = d(r, COL_BLOCK) & "|" & d(r, COL_TRIAL) 'create key
    dBT(k) = Application.AverageIf(d(r, COL_FT), (d(r, COL_AOI) = "AOI Entry"))
    End If
Next r

This is resulting in #VALUE! being printed in the relevant cells, rather than the expected number.

screenshot: enter image description here

What is causing this? What is the correct way to code this formula?

Upvotes: 1

Views: 328

Answers (1)

YowE3K
YowE3K

Reputation: 23994

Your current issue with the line

dBT(k) = Application.AverageIf(d(r, COL_FT), (d(r, COL_AOI) = "AOI Entry"))

is due to the fact that you are trying to take the average of a single value, and only if that value is True or False. E.g. when r is 1, your code equates to

dbt("Block 1|Trial, 8") = Application.AverageIf(-2484, ("" = "AOI Entry"))

or

dbt("Block 1|Trial, 8") = Application.AverageIf(-2484, False)

As none of the values in the average range (i.e. the value -2484) matches the criteria (i.e. False), the function attempts to divide the sum of matching values (i.e. 0) by the count of matching values (i.e. 0) and errors out.

Similarly, when r is 2, the code equates to

dbt("Block 1|Trial, 1") = Application.AverageIf(31, ("AOI Entry" = "AOI Entry"))

or

dbt("Block 1|Trial, 1") = Application.AverageIf(31, True)

Again, 31 does not equal True, and you end up trying to divide 0 by 0.

You would have got an answer (although not a meaningful answer) if you had used a formula of

dBT(k) = Application.AverageIf(d(r, COL_AOI), "AOI Entry", d(r, COL_FT))

which would have summed d(r, COL_FT) (the average range) if d(r, COL_AOI) (the range to test against the criteria) matched AOI Entry" (the criteria). (Summing a single number is a bit pointless, but it would still have done it.) However, this would still have given a division by zero error when d(r, COL_AOI) was not "AOI Entry", and would give a meaningless answer in the cases when it did work.

To get a meaningful average, you need to divide a sum of values by a count of values. Your code is not set up in a way that makes it easy to use Excel's built-in functions to calculate the sum and count, so you will need to calculate the sum and count yourself.

In the following code I have added two dictionaries (one called Sums, and the other called Cnts) to keep track of those numbers. The average can then be easily derived by dividing Sums(k) by Cnts(k).

I have also taken the liberty of changing your variable k to be an array. Your current code was calculating the key in at least 8 positions, so I changed it to calculate it once, and then use the same value in each other position.

Dim dBT As Object 'global dictionary

Sub buttonpresscount()
    Dim Sums As Object
    Dim Cnts As Object

    'constants for column positions
    Const COL_BLOCK As Long = 1
    Const COL_TRIAL As Long = 2
    Const COL_ACT As Long = 7
    Const COL_AOI As Long = 8
    Const COL_RT As Long = 16
    Const COL_FT As Long = 17

    Dim rng As Range, lastrow As Long, sht As Worksheet
    Dim d, r As Long, resBT()
    Dim k() As String

    Set sht = Worksheets("full test")
    lastrow = sht.Cells(Rows.Count, 3).End(xlUp).Row
    Set dBT = CreateObject("scripting.dictionary")
    Set Sums = CreateObject("scripting.dictionary")
    Set Cnts = CreateObject("scripting.dictionary")

    Set rng = sht.Range("B7:T" & lastrow)

    d = rng.Value  'get the data into an array

    ReDim resBT(1 To UBound(d), 1 To 1) 'resize the array which will
                                        '  be placed in ColT
    ReDim k(1 To UBound(d, 1)) As String

    'get unique combinations of Block and Trial and pressedcounts for each
    For r = 1 To UBound(d, 1)
        'Calculate the key once, then it can be used in every other loop
        k(r) = d(r, COL_BLOCK) & "|" & d(r, COL_TRIAL) 'create key
        dBT(k(r)) = dBT(k(r)) + IIf(d(r, COL_ACT) <> "", 1, 0)
    Next r

    'populate array with appropriate counts for each row
    For r = 1 To UBound(d, 1)
        resBT(r, 1) = dBT(k(r))        'get the count
    Next r

    'place array to sheet
    sht.Range("T7").Resize(UBound(resBT, 1), 1) = resBT

    'clear dictionary
    dBT.RemoveAll

    'count AOI entries
    For r = 1 To UBound(d, 1)
        If resBT(r, 1) = 1 Then    'only proceed with trials with 1 button press
            If d(r, COL_AOI) = "AOI Entry" Then
                dBT(k(r)) = dBT(k(r)) + 1     'get count
                Cnts(k(r)) = Cnts(k(r)) + 1   'get count
                Sums(k(r)) = Sums(k(r)) + d(r, COL_FT)   'sum column R
            End If
        Else
            dBT(k(r)) = ""
        End If
    Next r

    'populate array with appropriate counts for each row
    For r = 1 To UBound(d, 1)
        resBT(r, 1) = dBT(k(r))          'get the count
    Next r

    'place array to sheet
    sht.Range("U7").Resize(UBound(resBT, 1), 1) = resBT

    createsummarytable
    PopSummaryAOI dBT

    dBT.RemoveAll

    'retrieve and print reaction times to data summary sheet
    For r = 1 To UBound(d, 1)
        If resBT(r, 1) <> "" Then 'if buttonpresscount = 1 and AOI count exists
            dBT(k(r)) = d(r, COL_RT)
        End If
    Next r

    'Populate array with last row reaction time for each trial
    For r = 1 To UBound(d, 1)
        resBT(r, 1) = dBT(k(r))          'get the count
    Next r

    PopSummaryRT dBT

    dBT.RemoveAll

    'work out avg fixation time per trial
    For r = 1 To UBound(d, 1)
        If resBT(r, 1) <> "" Then
            If Cnts(k(r)) < 1 Then
                'Error if no results
                dBT(k(r)) = CVErr(xlErrDiv0)
            Else
                'Determine average
                dBT(k(r)) = Sums(k(r)) / Cnts(k(r))
            End If
        End If
    Next r

    'populate array
    For r = 1 To UBound(d, 1)
        resBT(r, 1) = dBT(k(r))          'get the count
    Next r

    PopSummaryFT dBT

End Sub

Upvotes: 1

Related Questions