Aging Date Average

Beemer Bum

Hi Community,

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.

Thoughts?

Paul.Koetke

Hi Beemer Bum, 

You're pretty darn close with that formula. You're going to need a calculation column to do this. That calc column will need the formula: 

=if(and(or([Request Type]="Value 1", [Request Type]="Value 2"),[Open Count]=1),[Days Open]

This column will then have the Days Open numbers for Value 1&2 items that are currently open. Now all your have to do is get an average of this calculation column. I recommend indenting all these rows and getting the average with:

=avg(children())

Then this average will be the number you post in your KPI dashboard.

 

Let me know if this does it. 

Paul Koetke

Upwork - Smartsheet Consultant

Beemer Bum

Hi Paul,

Thanks for the write-up.  I'll take it for a spin and let you know how it drives. 

Cheers.

 

UPDATE: it worked...mostly.  There is only one column that can be indented in any particular sheet and it happened that it was none of those I shared.  However, using the new aggregated column, I just average that since the correct values are there.  

Paul, thanks so much for the quick and thorough reply!