Average Collect Formula Not Calculating Correctly.
Hello,
I am looking to determine the average time based based on start/ completion dates, location and the frequency in which the activity occurs. However, it does not seem to be calculating based on my criteria. Could anyone help me determine why the average is not calculating based on my criteria?
=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"), "")
Answers
-
Are you getting an error or an incorrect result?
What exactly is in {FY 21 340b Activity Tracking Parent Level}? I ask because you are searching for text by using the quotes, but if your source data is numeric values then it may not pull.
-
Thanks, Paul. Forgot to mention I am also filtering by the level of the activity in the hierarchy. Is there a better way to do this?
-
It can be done. It just depends on how the data within that range is being populated. Is it manual entry or a formula?
-
The parent hierarchy level uses this formula to calculate : =COUNT(ANCESTORS()) + 1
-
Ok. In the source data, you are outputting a numerical value. In your formula you are searching for a text value by using the quotes. Try removing them from around the 3.
=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"), "")
-
It is still not calculating correctly - it's pulling in data from the annual activities rather then the monthly.
-
Ok. So that means this is the problem area...
{FY 21 340b Activity Tracking Activity Frequency}, "Monthly"
Exactly how is the data in that range populated?
-
Thanks for the assistance. It's pulling data based on a drop-down list that is single select.
-
Is it a formula or is it the actual dropdown that populates the range of {FY 21 340b Activity Tracking Activity Frequency}?
-
It's a drop-down.
-
Are you able to grab a screenshot of the column properties?
-
Thanks! Please see attached - I am also happy to share you to the sheets in question.
-
Lets see what happens when we try this formula...
=COUNTIFS({FY 21 340b Activity Tracking Activity Frequency}, "Monthly")
-
It counts them correctly. Could it be an error with something else in the formula?
-
Ok. Let's make sure the other counts are being pulled correctly as well...
=COUNTIFS({FY 21 340b Activity Tracking Location}, "APD")
=COUNTIFS({FY 21 340b Activity Tracking Parent Level}, 3)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!