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

fryeaf

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.

jmyzk_cloudsmart_jp

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

jmyzk_cloudsmart_jp

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