Metrics Sheet Formula to Calculate Average of Numbers in Column on Main Sheet
Hi,
I have a main sheet with the columns seen below.
As stated in the notes, I have not accounted for skipping the calculations when a date missing in a row. That's my first request  correcting the first 3 formulas.
Second, I have a metrics sheet where I want to have a formula calculate the average of the numbers in the Durations Sum column, which should contain only numbers for rows with all 5 dates. I've searched for calculating the average of a column of numbers, but I am either not finding the right kind of solutions or the formulas I have aren't being read by Smartsheet as numbers because I'm getting errors as I play around with it.
Thanks in advance for your help/guidance!
Lori
Best Answer

May need to change the number of ) I think I got all the If statements closed but may have miss counted. The below formula should leave the cell blank if any of the 4 dates are blank in that row and give you the number of network days between the date sent and the date received row. You would just need to change the last date sent@row and Date Received @row to your other cells for the Granted, Verified formulas.
=IFERROR(If([Date Sent]@row="","",IF([Date Received]@row="","",IF[Date Submitted]@row="","",IF([Date Granted]@row="","",IF[Date Verified]@row="","",NETWORKDAY([Date sent]@row,[Date Received]@row)))))),"")
Duration Sum Formula
=IFERROR(If([Date Sent]@row="","",IF([Date Received]@row="","",IF[Date Submitted]@row="","",IF([Date Granted]@row="","",IF[Date Verified]@row="","",Sum([sentReceived Duration]@row:[GrantedVerified Duration]@row)))))),"")
To get the average of the Durations Sum column you would use =AVG formula but you will need to set up your reference to that column if it is on another sheet.
=AVG({Your Duration Sum Reference})
Answers

May need to change the number of ) I think I got all the If statements closed but may have miss counted. The below formula should leave the cell blank if any of the 4 dates are blank in that row and give you the number of network days between the date sent and the date received row. You would just need to change the last date sent@row and Date Received @row to your other cells for the Granted, Verified formulas.
=IFERROR(If([Date Sent]@row="","",IF([Date Received]@row="","",IF[Date Submitted]@row="","",IF([Date Granted]@row="","",IF[Date Verified]@row="","",NETWORKDAY([Date sent]@row,[Date Received]@row)))))),"")
Duration Sum Formula
=IFERROR(If([Date Sent]@row="","",IF([Date Received]@row="","",IF[Date Submitted]@row="","",IF([Date Granted]@row="","",IF[Date Verified]@row="","",Sum([sentReceived Duration]@row:[GrantedVerified Duration]@row)))))),"")
To get the average of the Durations Sum column you would use =AVG formula but you will need to set up your reference to that column if it is on another sheet.
=AVG({Your Duration Sum Reference})

Hi Hollie,
Thank you for responding! Your formulas are exactly what I needed. I really appreciate your quick response!
Lori
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!