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
-
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.
Answers
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!