Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Avg of a range with 2 criteria
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!!
Best Answers
-
As always you are right! Thanks Paul!!
-
Answers
-
It is how you are referencing the year.
=AVG(COLLECT({Row Averages}, {QA Type}, "Internal", {QA Initial Date}, IFERROR(YEAR(@cell), 0) = 2020))
-
As always you are right! Thanks Paul!!
-
-
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?
-
@Stephanie Kiecker You are going to want to try something along these lines...
=AVG(COLLECT({Date Column}, @cell < TODAY(), {Number Column}, @cell > 0))
-
What if I wanted to calculate per week range?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm curious for some inspiration please! I'm using a form to log usage of machinery. This collects data on the time used in minutes, which I convert to hours and total for each machine. The machines n…
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…