Average between date ranges
Hello,
I need to find the average netdays between a service request date and an activity date if the service request date and the activity date fall between my dashboard start date and end date, and specific to a name@row. For example, I have 12 service Techs who get requests for repair work. When they fill out the form, they enter the requested date and the date they actually do the work. I need to find the average for a given date range. I set up my dashboard calculations page with a start and end date. I figured out how to do it for the YTD, but can't get it to calculate for me with additional criteria. Here is the formula that is working: =AVERAGEIF({FSE}, Name@row, {OOB Days to Close})
Here's what I am trying to do: =Averageif({FSE}, Name@row, {OOB Days to Close}, {Activity Date}, >=$[Beginning Date]$1, {Activity Date}, <=$[Ending Date]$1).
I wonder if a =netday function might work. Maybe with a nested if/then? I just need it to go average the days between two dates (service date and activity date) that are between my beginning date and end date and specific to the service tech (FSE). Thanks in advance for this help!
Answers
-
Hi @BFuller
An AVERAGEIF with multiple criteria would actually be a combination of AVG and COLLECT.
Ex:
=AVG(COLLECT({Column to Avg}, {Column 1 with Criteria}, "Criteria 1", {Column 2 with Criteria}, "Criteria 2"))
So in your case, try something like:
=AVG(COLLECT({OOB Days to Close}, {FSE}, Name@row, {Activity Date}, >=$[Beginning Date]$1, {Activity Date}, <=$[Ending Date]$1))
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!