Calculate the right average for the columns

RobNY2

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😀

Leibel S

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

Paul Newcome

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)

RobNY2

Paul

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

Rob

Leibel S

@Paul Newcome

I think you missed a couple of parentheses:

@RobNY2

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

Leibel S

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

RobNY2

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

Leibel S

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

RobNY2

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

Paul Newcome

@Leibel Shuchat Thanks!

RobNY2

Leibel