RESOLVED: AVG(Collect) Formula Returning #InvalidDataType

Kia Gibbs

Hello,

First, thank you to all the formula gurus who lend your time to help us with our broken formulas!

My Task: calculate average response time per fiscal year.

The Formula Used:

=AVG(COLLECT([Response Time]:[Response Time], [Allotted Plan Review Time]:[Allotted Plan Review Time], "30", [Fiscal Year]:[Fiscal Year], "2025"))

Formula Breakdown:

[Response Time]:[Response Time] - Range to Average

[Allotted Plan Review Time] - Criteria 1, search for allotted time that equals 30 days

[Fiscal Year] - search for fiscal year that is equal to 2025

Issue: I've reviewed and followed the rules of other avg(collect) questions/answers. The trouble in my formula begins when I include the Fiscal Year. Fiscal Year formula references a date column but returns a number (ex: 2025)

Fiscal Year Column is calculated as follows:

=IF([FYcal: Month]@row < 7, [FYCal: Year]@row, [FYCal: Year]@row + 1)

The sheet I am using is a running index from all time. I need averages per fiscal year. Any suggestions/recommendations would be greatly appreciated.

Corey W.

Are all the values in your Response Time column numbers?

Kia Gibbs

@Corey W. - Yes, but there are some blank spaces for rows that we have not yet responded to.

Corey W.

Try defaulting those to zero or adding a condition to your collect function to ignore the blanks. I think sometimes AVG functions get mad at non-numbers.

Kia Gibbs

I'm not so sure that's the issue.

I've calculated the average response time for all time with blank spaces and it returned a value. The problem is when I add the fiscal year portion to the formula. That's when the formula fails.

Nic Larsen

I'd also remove the quotes from around the 2025 and 30… you don't need those and it treats them as text vs a number.

Kia Gibbs

@Nic Larsen - Thanks for the response. Just tried that and still the same result.

Kia Gibbs

@Corey W. - piggybacking off your response about blank fields, further down in the sheet I found some error cells in the Fiscal Year column. Currently working to populate those cells with a date to see if that will fix the problem.

Will report back.

Kia Gibbs

@Corey W. - Thank you for mentioning the blank spaces!

I had to take a deep dive in my sheet to find some of the rows did not populate a Fiscal Year! Once I entered the information for the fiscal year to populate, it returned the necessary value. Thanks again!!