Calculate the right average for the columns

I need the average collect to calculate the right total % complete for the columns

image.png

Each column that has a date is = 100%

If there is no date is = 0% but the formula still counts the 0% to give the total % average complete on "Quote % Complete" column.

If there is "N/A" either in "1st Circuit Quote Status or 2nd Circuit Quote Status" column, the formula will skip the columns for either "1st or 2nd Circuit Quote" (the 3 columns on the left side of Circuit Quote Status) and calculate only the columns that don't have the Circuit Quote Status "N/A".

I hope someone can help me!

Thank you very much!

RobπŸ˜€

Best Answer

  • Leibel S
    Leibel S Community Champion
    Answer βœ“

    @RobNY2

    Try the below:

    =IF(AND([1st Circuit Quote Status]@row = "N/A", [2nd Circuit Quote Status]@row = "N/A"), 1, SUM(COUNTIFS([1st Circuit Quote Request]@row:[1st Circuit Quote Approved]@row, AND(ISDATE(@cell), [1st Circuit Quote Status]@row <> "N/A")) + COUNTIFS([2nd Circuit Quote Request]@row:[2nd Circuit Quote Approved]@row, AND(ISDATE(@cell), [2nd Circuit Quote Status]@row <> "N/A"))) / SUM(IF([1st Circuit Quote Status]@row <> "N/A", 3, 0) + IF([2nd Circuit Quote Status]@row <> "N/A", 3, 0)))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!