Aging Date Average
I am attempting to create a KPI for a dashboard that will capture the average number of days a 'something' has been opened, but only if it is still in an opened state. I can perform the appropriate "days open" with "=IF([Closed Count]3 = 1, NETWORKDAYS([Submitted Date]3, [End Date]3), NETWORKDAYS([Submitted Date]3, TODAY()))" and that works as expected. But, If I want to use column logic to aggregate, it all goes in the can.
So, I have 3 columns to deal with for criteria and/or data:
Request Type - has several options of which only two are what I care to use
Open Count - binary value that allows me to identify if open or not
Days Open - the number of days open per the calculation above
What I'm trying to do is:
=IF(OR([Request Type] = "Value 1", [Request Type] = "Value 2") AND [Open Count] = 1, AVE([Days Open], "0")
or something like that. I know the syntax is incorrect...consider it pseudo-code'ish.
What I should end up with is a single number representing the average number of days a set of requests have been open, for those that are currently open.