Aging Date Average

Beemer Bum
Beemer Bum ✭✭✭
edited 12/09/19 in Formulas and Functions

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.



  • Paul.Koetke
    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:


    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
    Beemer Bum ✭✭✭
    edited 11/01/18

    Hi Paul,

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



    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!