Average Collect Formula Not Calculating Correctly.

Katye Reed

Everything looks good!

Paul Newcome

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?

Katye Reed

Thanks - this is being calculated with the following formula:

=IFERROR(NETWORKDAYS(Start@row, [Actual End Date]@row), "")

Paul Newcome

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)

Katye Reed

That worked - it's calculating correctly.

Paul Newcome

Ok. Then what is the AVG/COLLECT generating vs what should it be?

Katye Reed

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?

Paul Newcome

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?

Katye Reed

I appreciate your assistance. No - it is still displaying incorrectly. I will submit a ticket.

Paul Newcome

Sorry I couldn't be of more help. Feel free to revisit this thread with the solution.

Katye Reed

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.

Paul Newcome

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.