Divide by Zero Help

JKP0009

Hello all!

I'm trying to calculate an average processing time when certain variables are met. Please see below:

=AVG(COLLECT({Support Processing Time}, {Created}, "2023", {Crew Ops Support Team Member}, "Trenton Graf", {Agility Number}, ""), COLLECT({Support Processing Time}, {Created}, "2023", {Crew Ops Support Team Member}, "Joseph Pierce", {Agility Number}, ""), COLLECT({Support Processing Time}, {Created}, "2023", {Crew Ops Support Team Member}, "Warren Smith", {Agility Number}, ""), COLLECT({Support Processing Time}, {Created}, "2023", {Crew Ops Support Team Member}, "Toni Ellis", {Agility Number}, ""))

Where:

• Support Processing Time is the total number of days that the support team took to process and complete a request OR send it to IT (formula for this column is "=IF(NOT(ISBLANK([IT Ref #]@row)), [Agility Creation Date]@row - [Created Date]@row, "") + IF(ISBLANK([IT Ref #]@row), [Completion Date]@row - [Created Date]@row, "")"
• Created is the year in which we received the request
• Crew Ops Support Team Member is the member of the team this request is assigned to (this is important because there are some requests we receive that are automated to other people)
• Agility # is a blank/not blank reference (The sheet column is named IT Ref #, as seen in the Support Processing Time formula.)

My first thought was since my average calculation and the Support Processing Time calculation both included a blank/not blank reference to the agility number, it might be causing interference. So I took the {Agility Number}, "" out of each part of the AVG calculation and received the same error.

I know there is a lot going on here, but any assistance that can be provided would be greatly appreciated!

Carson Penticuff

It seems like this should work as you have it setup. I would verify that all related columns are formatted correctly, especially {Support Processing Time}.

I don't see why your implementation would not work, as is, but you may try NETDAYS instead of subtracting dates.

=IF(NOT(ISBLANK([IT Ref #]@row)), NETDAYS([Created Date]@row, [Agility Creation Date]@row), "") + IF(ISBLANK([IT Ref #]@row), NETDAYS([Created Date]@row, Completion Date]@row),   "")

Lastly, I would ensure that no one has entered any incorrect dates.