+ Reply to Thread
Results 1 to 5 of 5

Using a Variable as a Criteria in the AVERAGEIF function

  1. #1
    Registered User
    Join Date
    10-22-2014
    Location
    Earth
    MS-Off Ver
    Office 2010
    Posts
    6

    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. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    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.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    10-22-2014
    Location
    Earth
    MS-Off Ver
    Office 2010
    Posts
    6

    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. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Using a Variable as a Criteria in the AVERAGEIF function

    Happy to help. Thanks for the feedback.

  5. #5
    Registered User
    Join Date
    01-04-2021
    Location
    Montreal
    MS-Off Ver
    16.44
    Posts
    1

    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!
    Last edited by mvalyear; 01-04-2021 at 11:19 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 08-28-2014, 02:44 AM
  2. AVERAGEIF function using reference as criteria range
    By AndySan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2013, 05:35 PM
  3. [SOLVED] Averageif for dates (variable year)
    By sherry_1991 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-25-2013, 02:17 PM
  4. variable averageif
    By freud1 in forum Excel General
    Replies: 0
    Last Post: 08-30-2011, 06:00 PM
  5. how to use a variable for the criteria in a sumif function?
    By Dick B. in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-01-2006, 08:00 AM

Bookmarks

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