Metrics Sheet Formula to Calculate Average of Numbers in Column on Main Sheet

Lori Flanigan

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

Hollie Green

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([sent-Received Duration]@row:[Granted-Verified 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})

Hollie Green

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([sent-Received Duration]@row:[Granted-Verified 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})

Lori Flanigan

Hi Hollie,

Thank you for responding! Your formulas are exactly what I needed. I really appreciate your quick response!

Lori