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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!