Avg of a range with 2 criteria



I am trying to average a range (which are percentages) based on the Issue Date Year and the Type.

I have tried:

=AVG(COLLECT({Row Averages}, {QA Type}, "Internal", {QA Initial Date}, Year=2020))


=AVERAGEIF({QA Type}:{QA Type}, "Internal", {QA Initial Date}:{QA Initial Date}, Year=2020, {Row Averages}:{Row Averages})

and any combo you can think of :) .. still can't get it to return nicely.


Best Answers


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    It is how you are referencing the year.

    =AVG(COLLECT({Row Averages}, {QA Type}, "Internal", {QA Initial Date}, IFERROR(YEAR(@cell), 0) = 2020))

  • Accepted Answer

    As always you are right! Thanks Paul!!

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Happy to help. 👍️

  • Stephanie KieckerStephanie Kiecker ✭✭✭✭✭

    On a similar note, my dilema is that I need to find the average for each of the columns below...lets start with JUST column One:

    I need to find the Average of this column based on two sets of criteria:

    1) I do not want to include zeros in the average


    2) I want to calculate the averages only for rows dated BEFORE today, which as of today is 1/18/21 (you can see that after these dates, all rows are zero - each day they populate with the number of tasks completed that day...so they are zero now...tomorrow if any tasks area completed a number will show)

    So basically, my the lists will grow to show how many tasks were performed by each dept listed in the Columns

    Can anyone assist with this dilema?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Stephanie Kiecker You are going to want to try something along these lines...

    =AVG(COLLECT({Date Column}, @cell < TODAY(), {Number Column}, @cell > 0))

Sign In or Register to comment.