Aging Date Average

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?

Find more posts tagged with

Sheets

Formulas

Project Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…