Avg of a range with 2 criteria

Elizabeth Aird

Hi!

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))

and

=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.

Thanks!!

Elizabeth Aird

As always you are right! Thanks Paul!!

Paul Newcome

Happy to help. 👍️

Paul Newcome

It is how you are referencing the year.

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

Elizabeth Aird

As always you are right! Thanks Paul!!

Paul Newcome

Happy to help. 👍️

Stephanie 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

and

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 Newcome

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

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

Rachel Rudd

https://community.smartsheet.com/discussion/comment/274080#Comment_274080

What if I wanted to calculate per week range?