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

✭✭✭✭✭
edited 05/23/23

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.

Lori

Tags:

• ✭✭✭✭✭✭

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.

Duration Sum Formula

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.

• ✭✭✭✭✭✭

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.

Duration Sum Formula

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.

• ✭✭✭✭✭
edited 05/24/23

Hi Hollie,

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

Lori

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!