Calculating Avg Workdays w/ Conditions
Hello!
I'm currently trying to calculate the average amount of workdays it takes for each person to complete a specific request on a metrics sheet from a parent sheet. For example: How long does it take me on average to complete a FOIA request? How long does it take me on average complete a litigation request?
Request Type = Type of Request (In this instance, FOIA)
AVG WDAYS = Column from parent sheet that has a formula calculating the average workdays for each specific line item to be completed
PLU Assigned = Person assigned to this request
=IFERROR((AVG({AVG WDAYS} = "", "", IF({Request Type}, CONTAINS("FOIA", @cell), {PLU Assigned}, HAS(@cell, "Bridgett Fenner")))))
I've made several adjustments since this morning, and moved around parentheses.
I'd be grateful for any insight! Thank you in advance. :)
Answers
-
Your "if" statement is not correctly stated.
Use a Collect to agglomerate data that you want to calculate.
=AVG(Collect([what you want to average], Critera range1, Critera1, criteria range 2, criteria2, ... etc
=AVG(COLLECT({AVG WDAY},{Request Type},="FIOA",{PLU Assigned},="Bridgett Fenner"))
-
The COLLECT function! That makes complete sense since I've never heard of it.
=AVG(COLLECT({AVG WDAYS}, {Request Type}, ="FOIA", {PLU Assigned}, ="Bridgett Fenner"))
I'm closer, but I'm still getting INCORRECT ARGUMENT. :(
-
does your arguments in brackets {} are columns from the same sheet?
-
That is correct. They're all from the same sheet.
-
Make sure each of the range references have the same number of cells in them.
-
To make sure the collect function does what you want, use the JOIN function and look at the result in a cell
=Join(collect(...),char(10))
The char(10) is a carriage return and it make it easier to read if you set the cell's text to wrap.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!