Calculate the right average for the columns

RobNY2
RobNY2 ✭
inFormulas and Functions

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

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @RobNY2

    试试以下:

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

    · Share on Facebook Share on Twitter

Answers

  • Paul Newcome
    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)

    10xViz.com

    · Share on Facebook Share on Twitter
  • RobNY2
    RobNY2 ✭

    Paul

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

    image.png

    Rob

    · Share on Facebook Share on Twitter
  • Leibel S
    Leibel S ✭✭✭✭✭✭
    edited 09/02/22

    @Paul Newcome

    I think you missed a couple of parentheses:

    @RobNY2

    试试以下:

    =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

    · Share on Facebook Share on Twitter
  • Leibel S
    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))

    · Share on Facebook Share on Twitter
  • RobNY2
    RobNY2 ✭
    edited 09/02/22

    Leibel

    The first formula it showed correct

    image.png

    the second formula I got this

    image.png

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

    Rob

    · Share on Facebook Share on Twitter
  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @RobNY2

    试试以下:

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

    · Share on Facebook Share on Twitter
  • RobNY2
    RobNY2 ✭

    Leibel


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


    非常感谢你的帮助和智慧

    Rob

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Leibel ShuchatThanks!

    10xViz.com

    · Share on Facebook Share on Twitter
  • RobNY2
    RobNY2 ✭
    edited 09/15/22

    Leibel

    image.png


    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册template!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
=[End Date]@row - [Start Date]@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108271,"type":"question","name":"Conditional formula - if a date is less than 30 days from today","excerpt":"I'm trying to create a conditional formula to flag if less than 30 days from today. I create a separate column to calculate but I am getting an error on the formula. =IF(AND([Event Date] > TODAY(), [Event Date] <= TODAY() + 30), \"Less than 30 days from today\", \"More than 30 days from today\") Help?","snippet":"I'm trying to create a conditional formula to flag if less than 30 days from today. I create a separate column to calculate but I am getting an error on the formula.…","categoryID":322,"dateInserted":"2023-07-28T18:21:58+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T19:22:21+00:00","insertUserID":159884,"insertUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":159884,"lastUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":23,"score":null,"hot":3381143059,"url":"https:\/\/community.smartsheet.com\/discussion\/108271\/conditional-formula-if-a-date-is-less-than-30-days-from-today","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108271\/conditional-formula-if-a-date-is-less-than-30-days-from-today","format":"Rich","lastPost":{"discussionID":108271,"commentID":387894,"name":"Re: Conditional formula - if a date is less than 30 days from today","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387894#Comment_387894","dateInserted":"2023-07-28T19:22:21+00:00","insertUserID":159884,"insertUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T19:21:56+00:00","dateAnswered":"2023-07-28T18:55:42+00:00","acceptedAnswers":[{"commentID":387890,"body":"

Try this:<\/p>

=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/94976/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":36,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"

Greetings @brownrobe<\/a>,<\/p>

Here is a possible solution for your formula:<\/p>

=IF(NOT(ISBLANK([Recovery Collected Date]@row)), \"Complete\", IF(AND(ISBLANK([Recovery Collected Date]@row), ISBLANK([Anticipated Collection Date]@row)), \"Incomplete\", IF(AND(NOT(ISBLANK([Anticipated Collection Date]@row)), ISBLANK([Recovery Collected Date]@row)), \"Pending\", \"//www.santa-greenland.com/community/discussion/94976/\")))<\/p>

Please confirm I have your column names correct and adjust as needed.<\/p>

I hope this helps, and have a great weekend.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • How do you calculate the average number of days between a submitted date and a start date?

    Additional context: We use Smartsheet to track support requests from the organization. We have an automated column that records the day the form is filled out. The form also has a date range of when they need their request completed by. I would like to calculate the average amount of days people provide from the day they…

  • Conditional formula - if a date is less than 30 days from today

    I'm trying to create a conditional formula to flag if less than 30 days from today. I create a separate column to calculate but I am getting an error on the formula. =IF(AND([Event Date] > TODAY(), [Event Date] <= TODAY() + 30), "Less than 30 days from today", "More than 30 days from today") Help?

  • Combining IF Formula for Blank/ Not Blank Cells

    I want to create a formula that provides the below statuses: -Complete: Based on "Collected Date" not null -Incomplete: Based on "Collected Date" null and "Antcipated Collected Date" null -Pending: Based on "Anticipated Collcted Date" not null and "Collected Date" null Below is what I have, but it's unparseable:…