# Using a Variable as a Criteria in the AVERAGEIF function

1. ## Using a Variable as a Criteria in the AVERAGEIF function

Hi all,

I am trying to write a macro that iterates through a dataset and averages values in one column based on values found in another column. I've been trying to use the AVERAGEIF function, but I cannot for the life of me figure out how to use a variable (in this case, variable i) in the function.

Here's my code snippet:

-----------------

Dim i As Long
Dim z As Long

z = 13

For i = 400 To 1000

'Set the D column value to the iteration number
Range("D" & z).Value = i

'Set the corresponding cell in the E column to the average of the corresponding values

Range("E" & z).Select
ActiveCell.Formula = "=AVERAGEIF(\$A\$13:\$A\$3173, " = "&i, \$B\$13:\$B\$3173)"

z = z + 1

Next

-------------------

This returns a value of FALSE where the AVERAGEIF output should go.

I've also tried it in the form of:

=AVERAGEIF(\$A\$13:\$A\$3173, i, \$B\$13:\$B\$3173)

This returns a value of #DIV/0.

I also tried putting the a reference to a cell containing an absolute version of the i variable's value, like this:

=AVERAGEIF(\$A\$13:\$A\$3173, "="&D13, \$B\$13:\$B\$3173)

And it still returns a value of false.

The only time I can actually get it to work is when I input the numerical value (Eg. 400 instead of i), but I really don't feel like typing out this chunk of code 600 times - that's why I am doing the iterative For loop.

Could anyone please help me understand how to use this variable in the AVERAGEIF function? I've been trying this for hours and cannot figure it out.

Thanks!

2. ## Re: Using a Variable as a Criteria in the AVERAGEIF function

Please use CODE tags when you post code.

try:
``Please Login or Register  to view this content.``

3. ## Re: Using a Variable as a Criteria in the AVERAGEIF function

Sorry for neglecting to use CODE tags.

Your method worked like a charm! Thank you so much, that was an amazingly fast reply!

4. ## Re: Using a Variable as a Criteria in the AVERAGEIF function

Happy to help. Thanks for the feedback.

5. ## Re: Using a Variable as a Criteria in the AVERAGEIF function

Hello,

I am trying to do something very similar and can't get it to work. Any chance you could tell me what's wrong with this code?

Sub macro2()

Dim i As Integer

For i = 3 To 52
Cells(i, 16).Formula = "=AVERAGEIFS(R3C6:R2003C6,R3C6:R2003C6,"">0"",R3C5:R2003C5,""=20"",R3C12:R2003C12,""&i&"")"

Next i

End Sub

Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1