Need Help with AVG(Collect

James Gilfillan

Hey all,

The formula below works great but I need to add criteria to pull the average. As soon as I add a second criteria, I get invalid data type.

=AVG(COLLECT([Days to Release/On Hold]:[Days to Release/On Hold], [Origination Org]:[Origination Org], "Medford"))

This formula is what I am attempting to do.

=AVG(COLLECT([Days to Release/On Hold]:[Days to Release/On Hold], [Origination Org]:[Origination Org], "Medford", [Hold Department]:[Hold Department], "Canning", Month:Month, "1"))

I added criteria of Hold department being Canning and My month column (Which gives a number to each month for me) to be 1 for January.

Any advice on how to make this work with my added criteria while adding as few helper columns as possible?

Michelle Choate 2

Hello @James Gilfillan !!

The error #INVALID DATA TYPE is saying that the formula either contains or references an incompatible data type, such as [Origination Org] is a multi-dropdown. This leads me to believe that there may be something in your columns that is unable to be averaged.

Possibly in the [Month] column. Do you have any text in that column, or are any of the values returned by a formula? It would help if we could see a screen capture of your sheet, but please block out any sensitive data.

If you want to trouble shoot together, feel free to put time on my calendar and we can do a quick zoom call.