Average of figures in a cell range when the cells are a formula to calculate days past

Is there a formula to calculate the average when the cells in a range contain numbers? The cells contain a formula to calculate the number of days from one date to another, but I want the running average while entries within that range are populated over time, ie I don't want to have to readjust the formula when a line is added into the range. I can get close to what I need, but not there yet. TIA!

OG formula is: =AVG([Days from Award Processed to Account Creation]473:[Days from Award Processed to Account Creation]479)

Scenario: Row 480 (and Rows 473-479) contains the formula =NETDAYS([Award Processed / Memo sent from SPA to Accounting]@row, [Account Generation Date/Email Received]@row), so without other data in Row 480, the result in that cell is "#INVALID DATA TYPE"

Ideally, I want the average formula to work for all the cells in Rows 473-480 no matter how many Rows contain data.

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion
    Answer ✓

    Hi @fryeaf

    You can avoid the NETDAYS function by making sure both input values are dates by a formula like this;

    =IF(AND(ISDATE([Award Processed / Memo sent from SPA to Accounting]@row), ISDATE([Account Generation Date/Email Received]@row)), NETDAYS([Award Processed / Memo sent from SPA to Accounting]@row, [Account Generation Date/Email Received]@row))

    or

    =IFERROR(NETDAYS([Award Processed / Memo sent from SPA to Accounting]@row, [Account Generation Date/Email Received]@row), "")

    Then, you can use the AVG function, including the row without the date values.

    My example below uses the column formulas, but the principle is the same.

    https://app.smartsheet.com/b/publish?EQBCT=cf668e3e503849e0b7ab8f3353ae4c58

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion
    Answer ✓

    Hi @fryeaf

    You can avoid the NETDAYS function by making sure both input values are dates by a formula like this;

    =IF(AND(ISDATE([Award Processed / Memo sent from SPA to Accounting]@row), ISDATE([Account Generation Date/Email Received]@row)), NETDAYS([Award Processed / Memo sent from SPA to Accounting]@row, [Account Generation Date/Email Received]@row))

    or

    =IFERROR(NETDAYS([Award Processed / Memo sent from SPA to Accounting]@row, [Account Generation Date/Email Received]@row), "")

    Then, you can use the AVG function, including the row without the date values.

    My example below uses the column formulas, but the principle is the same.

    https://app.smartsheet.com/b/publish?EQBCT=cf668e3e503849e0b7ab8f3353ae4c58

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!