Average Collect Formula Not Calculating Correctly.

Katye Reed

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"), "")

Paul Newcome

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.

Katye Reed

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?

Paul Newcome

It can be done. It just depends on how the data within that range is being populated. Is it manual entry or a formula?

Katye Reed

The parent hierarchy level uses this formula to calculate : =COUNT(ANCESTORS()) + 1

Paul Newcome

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"), "")

Katye Reed

It is still not calculating correctly - it's pulling in data from the annual activities rather then the monthly.

Paul Newcome

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?

Katye Reed

Thanks for the assistance. It's pulling data based on a drop-down list that is single select.

Paul Newcome

Is it a formula or is it the actual dropdown that populates the range of {FY 21 340b Activity Tracking Activity Frequency}?

Katye Reed

It's a drop-down.

Paul Newcome

Are you able to grab a screenshot of the column properties?

Katye Reed

properties.PNG

Thanks! Please see attached - I am also happy to share you to the sheets in question.

Paul Newcome

Lets see what happens when we try this formula...

=COUNTIFS({FY 21 340b Activity Tracking Activity Frequency}, "Monthly")

Katye Reed

It counts them correctly. Could it be an error with something else in the formula?

Paul Newcome

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)