Average number of day formula

FGCC Smartsheet

I have these two columns "Date Created" and "Date Closed". I need to write a formula that looks at all 1000+ rows of data and produces an average amount of days it takes to close a row of data. The formula will be in my calculations sheet. So, I'll be referencing this data. Should I use the "averageifs"? I really appreciate the assistance with this formula.

Anjanesh Vaidya

Hi FGCC Smartsheet,

To round the average you can use ROUND function.

=ROUND(AVG([No. of Days]1:[No. of Days]30), 0)

Thank You!

Anjanesh Vaidya

FGCC Smartsheet

I've tried several different formula and they all return unparesable.

=AVERAGEIF({File a complaint Range 1}, "<="&TODAY(), {complaints Range 2}, ">="&TODAY(), "<>")

=AVERAGEIF({File a complaint Range 1}, "<="&TODAY(), {complaints Range 2}, ">="&TODAY(), {File a complaint Range 1}, "<>", "")

=COUNTIFS({File a complaint Range 1}, ">="&TODAY(), {File a complaint Range 1}, "<="&TODAY())

Thanks!

Anjanesh Vaidya

Hi FGCC Smartsheet,

To get the average amount of days from the rows you can try following formula-

First, we need to create a helping column "No. of Days" where we will use following formula-

=[Date Closed]@row - [Date Created]@row

Then, in "Average Calculation" column we will use following formula-

=AVG([No. of Days]1:[No. of Days]30)

Here, you can use the Average Calculation formula in your calculation sheet as mentioned and provide the range accordingly as you mentioned 1000+ rows.

I Hope it will work!

Thank You!

Anjanesh Vaidya

FGCC Smartsheet

Thanks for that. It almost works exactly right. Only issues I'm having are:

Regards

Anjanesh Vaidya

Hi FGCC Smartsheet,

To round the average you can use ROUND function.

=ROUND(AVG([No. of Days]1:[No. of Days]30), 0)

Thank You!

Anjanesh Vaidya

FGCC Smartsheet

@Anjanesh Vaidya Thank you!