Average Formulas

Apolonio Fama

Hi all,

Need help with trying to get a formula for Averaging Transit Days for certain criterias in Carrier and Origin. Ultimately I'm trying to build a dashboard of transit days for each origin location. Below if formula I am trying, but I'm not getting any output.

=AVG(COLLECT([Transit Days]:[Transit Days], Carrier:Carrier, "DM", Origin:Origin, "HKG"))

Paul Newcome

Ok. Start by checking your [Freight received] and [Delivery Date columns. Are there any errors or missing dates? If so, that would cause an error in your NETWORKDAY formula which would be pulled through into your average formula.

Paul Newcome

When you say you are "not getting any output" do you mean that the formula is returning a blank or are you getting an error, or...?

Apolonio Fama

Hi Paul,

Sorry it's giving me error "#Invalid Data Type". Any information is greatly appreiated :).

Paul Newcome

Do you have that error in any of the columns being referenced? How is the data in the [Transit Days] column being populated?

Apolonio Fama

Yes, i do have that in the [transit day] column which is calculating the below formula. Please note that's just one sample with correct data.

Transit Days formula below

=NETWORKDAY([Freight received]24, [Delivery Date]24)

Paul Newcome

Ok. Start by checking your [Freight received] and [Delivery Date columns. Are there any errors or missing dates? If so, that would cause an error in your NETWORKDAY formula which would be pulled through into your average formula.

Apolonio Fama

Thanks Paul for the assistance.  I was able to fix the issues with your help.  Much appreciated.

Paul Newcome

Happy to help. 👍️