Average Collect Formula Not Calculating Correctly.
Answers
-
Everything looks good!
-
Great. So we have narrowed it down to this range.
{FY 21 340b Activity Tracking Days to Completion}
This is the data you are actually collecting for the AVG function. Exactly how is this data being populated?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks - this is being calculated with the following formula:
=IFERROR(NETWORKDAYS(Start@row, [Actual End Date]@row), "")
-
Well that is producing numerical values, so it should be working correctly. Let's try expanding our COUNTIFS...
=COUNTIFS({FY 21 340b Activity Tracking Activity Frequency}, "Monthly", {FY 21 340b Activity Tracking Location}, "APD", {FY 21 340b Activity Tracking Parent Level}, 3)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
That worked - it's calculating correctly.
-
Ok. Then what is the AVG/COLLECT generating vs what should it be?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It's calculating a number, -27 days which is correct for the MHMH location annual activity type. But it is calculating this for all activity types and locations which is incorrect. Would it help if I shared the metric sheet with you?
-
Hmm... It seems like it is something that is lingering on the back-end.
If this does not work, I am going to have to suggest reaching out to support.
Remove incorrect formula(s)
Log out
Clear cookies and cache
Log back in
Re-enter one of the formulas to see if it is working correctly
Does that help any?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I appreciate your assistance. No - it is still displaying incorrectly. I will submit a ticket.
-
Sorry I couldn't be of more help. Feel free to revisit this thread with the solution.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I reached out to support and it appears I had an extra parenthesis which caused the formula to exclude on of the criteria.
Correct Formula: =IFERROR(AVG(COLLECT({FY 21 340b Activity Tracking Days to Completion}, {FY 21 340b Activity Tracking Parent Level}, 3, {FY 21 340b Activity Tracking Activity Frequency}, "Monthly", {FY 21 340b Activity Tracking Location}, "APD")), "")
Original Formula: =IFERROR(AVG(COLLECT({FY 21 340b Activity Tracking Days to Completion}, {FY 21 340b Activity Tracking Parent Level}, "3"), {FY 21 340b Activity Tracking Activity Frequency}, "Monthly", {FY 21 340b Activity Tracking Location}, "APD"), "")
Thanks so much for all of your help.
-
Ugh. That's usually one of the first things I look for too. Sorry for missing that. Glad you were able to get it working.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 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!