# Calculate the right average for the columns

✭✭

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

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😀

• ✭✭✭✭✭✭

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)))

• ✭✭✭✭✭✭

Try:

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

• ✭✭

Paul

I added the formula to and gave me this result below. Actually the all rows should be 100%

Rob

• ✭✭✭✭✭✭
edited 09/02/22

I think you missed a couple of parentheses:

Try the below:

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

Please note this formula never skips the 1st Circuit Quote

• ✭✭✭✭✭✭

Below is a formula example that would incorporate the "N/A" check also on the '1st circuit'

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

• ✭✭
edited 09/02/22

Leibel

The first formula it showed correct

the second formula I got this

So I want to incorporate the N/A for both 1st and 2nd Quote status columns.

Rob

• ✭✭✭✭✭✭

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)))

• ✭✭

Leibel

It seems to work. If I face any problems I will let you know.

Thank you so much for your help and intelligence 😉

Rob

• ✭✭✭✭✭✭
• ✭✭
edited 09/15/22

Leibel

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!