Average for both columns

RobNY2
RobNY2 ✭
edited 09/22/22 inFormulas and Functions

I have a dropdown columns with several choices and each choice represents % complete. The idea is to get the Circuit % complete for 1st and 2nd circuits columns but also It has ‘N/A” that will not count. It only count the column that doesn’t have it.

This is what I tried:

=AVG(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1), IF([1st Circuit Status]@row = "Circuit Delivery", 1),IF([1st Circuit Status]@row = "Circuit in Construction Phase", .8),IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1),IF([2nd Circuit Status]@row = "Circuit Delivery", 1),IF([2nd Circuit Status]@row = "Circuit in Construction Phase", .8)

image.png








If you could help me, I really appreciate it!

Rob

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi@RobNY2

    If I'm understanding you correctly, you have one specific value that you want to assign as a percentage per choice, and there are two columns where these values could appear. In each row you want to AVG the two values from those two cells for a final percentage.

    If this is the case, I believe you're missing a comma from the first formula. This is the structure:

    =SUM(first value,second value) / 2

    However notice that when you swap from doing an IF statement for the 1st Circuit Status there's no comma. It should be:

    =SUM(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([1st Circuit Status]@row = "Circuit Delivery", 1) + IF([1st Circuit Status]@row = "Circuit in Construction Phase", 0.8), IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([2nd Circuit Status]@row = "Circuit Delivery", 1) + IF([2nd Circuit Status]@row = "Circuit in Construction Phase", 0.8)) / 2


    Since you have a much longer list, what I would suggest doing is two Nested IF statements (assuming that you'll only ever have one value per cell).

    This way you can group together all the same values that have the same output with an IF(OR statement, like so:

    IF(OR([1st Circuit Status]@row= "Contract in Process", [1st Circuit Status]@row= "Waiting for Quote", [1st Circuit Status]@row= "Waiting for Order Validation to complete", [1st Circuit Status]@row= "Cancelled", [1st Circuit Status]@row= "Requirement Review", [1st Circuit Status]@row= "Not Started", [1st Circuit Status]@row= "Out of Scope", [1st Circuit Status]@row= "Reassigned to another Carrier", [1st Circuit Status]@row= "Need to reassign to another Carrier", [1st Circuit Status]@row= "Addendum 37"), 0,


    For a full formula, you could do something like this:

    =SUM(

    IF(OR([1st Circuit Status]@row= "Contract in Process", [1st Circuit Status]@row= "Waiting for Quote", [1st Circuit Status]@row= "Waiting for Order Validation to complete", [1st Circuit Status]@row= "Cancelled", [1st Circuit Status]@row= "Requirement Review", [1st Circuit Status]@row= "Not Started", [1st Circuit Status]@row= "Out of Scope", [1st Circuit Status]@row= "Reassigned to another Carrier", [1st Circuit Status]@row= "Need to reassign to another Carrier", [1st Circuit Status]@row= "Addendum 37"), 0, IF([1st Circuit Status]@row= "Started", .01, IF([1st Circuit Status]@row= "Waiting to assign to local carrier", .05, IF([1st Circuit Status]@row= "Circuit Order in process", .04, IF([1st Circuit Status]@row= "Assigned to Local Carrier", .10, IF([1st Circuit Status]@row= "Circuit in Design Phase", .6, IF([1st Circuit Status]@row= "Circuit Site Survey in Progress", .65, IF([1st Circuit Status]@row= "Circuit Site Survey Complete",.70, IF([1st Circuit Status]@row= "LCON Waiting Carrier to provide Design", .71, IF([1st Circuit Status]@row= "On Hold", .50, IF([1st Circuit Status]@row= "Waiting for LCON to Approve Site Survey", .73, IF([1st Circuit Status]@row= "Circuit in Construction Phase", .8, IF([1st Circuit Status]@row= "Circuit installed waiting carrier to deliver", .99, IF(OR([1st Circuit Status]@row= "Circuit Delivered", [1st Circuit Status]@row= "Use local Current Circuits"), 1),

    IF(OR([2nd Circuit Status]@row= "Contract in Process", [2nd Circuit Status]@row= "Waiting for Quote", [2nd Circuit Status]@row= "Waiting for Order Validation to complete", [2nd Circuit Status]@row= "Cancelled", [2nd Circuit Status]@row= "Requirement Review", [2nd Circuit Status]@row= "Not Started", [2nd Circuit Status]@row= "Out of Scope", [2nd Circuit Status]@row= "Reassigned to another Carrier", [2nd Circuit Status]@row= "Need to reassign to another Carrier", [2nd Circuit Status]@row= "Addendum 37"), 0, IF([2nd Circuit Status]@row= "Started", .01, IF([2nd Circuit Status]@row= "Waiting to assign to local carrier", .05, IF([2nd Circuit Status]@row= "Circuit Order in process", .04, IF([2nd Circuit Status]@row= "Assigned to Local Carrier", .10, IF([2nd Circuit Status]@row= "Circuit in Design Phase", .6, IF([2nd Circuit Status]@row= "Circuit Site Survey in Progress", .65, IF([2nd Circuit Status]@row= "Circuit Site Survey Complete",.70, IF([2nd Circuit Status]@row= "LCON Waiting Carrier to provide Design", .71, IF([2nd Circuit Status]@row= "On Hold", .50, IF([2nd Circuit Status]@row= "Waiting for LCON to Approve Site Survey", .73, IF([2nd Circuit Status]@row= "Circuit in Construction Phase", .8, IF([2nd Circuit Status]@row= "Circuit installed waiting carrier to deliver", .99, IF(OR([2nd Circuit Status]@row= "Circuit Delivered", [2nd Circuit Status]@row= "Use local Current Circuits"), 1)

    /2


    As a final note, make sure that any time you have a quotation mark in a formula it's straight up and down like so: "

    I notice that some of your quotes are curvy (” versus ") which would result in an error in Smartsheet.

    Cheers,

    Genevieve

    · Share on Facebook Share on Twitter

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @RobNY2

    See below option:

    =SUM(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([1st Circuit Status]@row = "Circuit Delivery", 1) + IF([1st Circuit Status]@row = "Circuit in Construction Phase", 0.8) + IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([2nd Circuit Status]@row = "Circuit Delivery", 1) + IF([2nd Circuit Status]@row = "Circuit in Construction Phase", 0.8)) / 2

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

    Leibel

    I am getting UNPARSEABLE.

    The 1st formula below is what you gave me and it works:

    =SUM(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([1st Circuit Status]@row = "Circuit Delivery", 1) + IF([1st Circuit Status]@row = "Circuit in Construction Phase", 0.8) + IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([2nd Circuit Status]@row = "Circuit Delivery", 1) + IF([2nd Circuit Status]@row = "Circuit in Construction Phase", 0.8)) / 2

    The 2nd formula below is what you gave me I just changed what is between" "and removed some spaces. It didn't work but your formula above without the spaces also worked, so the problem it is not removing the spaces:

    =SUM(IF([1st Circuit Status]@row="Assigned to Local Carrier",0.1)+IF([1st Circuit Status]@row="Circuit Delivery",1)+IF([1st Circuit Status]@row ="Circuit in Construction Phase",0.8)+IF([2nd Circuit Status]@row="Assigned to Local Carrier",0.1)+IF([2nd Circuit Status]@row ="Circuit Delivery",1)+IF([2nd Circuit Status]@row="Circuit in Construction Phase",0.8))/2

    This 3rd formula below is what I really need to insert in the cell. I removed the spaces because it is a big formula. Basically I copied what it is for "1st Circuit Status" and at the end for the formula "1st Circuit Status" I added the same formula but I changed to "2nd Circuit Status" and it didn't work:

    =SUM(IF([1电路状态]@row =“合同过程”,0)+IF([1st Circuit Status]@row=“Waiting for Quote”,0)+IF([1st Circuit Status]@row=“Waiting for Order Validation to complete”,0)+IF([1st Circuit Status]@row=“Cancelled”,0)+IF([1st Circuit Status]@row=“Requirement Review”,0)+IF([1st Circuit Status]@row=“Not Started”,0)+IF([1st Circuit Status]@row=“Out of Scope”,0)+IF([1st Circuit Status]@row=“Started”,.01)+IF([1st Circuit Status]@row=“Waiting to assign to local carrier”,.05)+IF([1st Circuit Status]@row=“Circuit Order in process”,.04)+IF([1st Circuit Status]@row=“ Reassigned to another Carrier”,0)+IF([1st Circuit Status]@row=“Assigned to Local Carrier”,.10)+IF([1st Circuit Status]@row=“Circuit in Design Phase”,.6)+IF([1st Circuit Status]@row=“Circuit Site Survey in Progress”,.65)+IF([1st Circuit Status]@row=“Circuit Site Survey Complete”,.70)+IF([1st Circuit Status]@row=“LCON Waiting Carrier to provide Design”,.71)+IF([1st Circuit Status]@row=“On Hold”,.50)+IF([1st Circuit Status]@row=“Waiting for LCON to Approve Site Survey”,.73)+IF([1st Circuit Status]@row=“Circuit in Construction Phase”,.8)+IF([1st Circuit Status]@row=“Circuit installed waiting carrier to deliver”,.99)+IF([1st Circuit Status]@row=“Circuit Delivered”,1)+IF([1st Circuit Status]@row=“Use local Current Circuits”,1)+IF([1st Circuit Status]@row=“Need to reassign to another Carrier”,0)+IF([1st Circuit Status]@row=“Addendum 37”,0)+如果([第二电路状态]@row =“合同过程”,0)+IF([2nd Circuit Status]@row=“Waiting for Quote”,0)+IF([2nd Circuit Status]@row=“Waiting for Order Validation to complete”,0)+IF([2nd Circuit Status]@row=“Cancelled”,0)+IF([2nd Circuit Status]@row=“Requirement Review”,0)+IF([2nd Circuit Status]@row=“Not Started”,0)+IF([2nd Circuit Status]@row=“Out of Scope”,0)+IF([2nd Circuit Status]@row=“Started”,.01)+IF([2nd Circuit Status]@row=“Waiting to assign to local carrier”,.05)+IF([2nd Circuit Status]@row=“Circuit Order in process”,.04)+IF([2nd Circuit Status]@row=“ Reassigned to another Carrier”,0)+IF([2nd Circuit Status]@row=“Assigned to Local Carrier”,.10)+IF([2nd Circuit Status]@row=“Circuit in Design Phase”,.6)+IF([2nd Circuit Status]@row=“Circuit Site Survey in Progress”,.65)+IF([2nd Circuit Status]@row=“Circuit Site Survey Complete”,.70)+IF([2nd Circuit Status]@row=“LCON Waiting Carrier to provide Design”,.71)+IF([2nd Circuit Status]@row=“On Hold”,.50)+IF([2nd Circuit Status]@row=“Waiting for LCON to Approve Site Survey”,.73)+IF([2nd Circuit Status]@row=“Circuit in Construction Phase”,.8)+IF([2nd Circuit Status]@row=“Circuit installed waiting carrier to deliver”,.99)+IF([2nd Circuit Status]@row=“Circuit Delivered”,1)+IF([2nd Circuit Status]@row=“Use local Current Circuits”,1)+IF([2nd Circuit Status]@row=“Need to reassign to another Carrier”,0)+IF([2nd Circuit Status]@row=“Addendum 37”,0))/2

    Below are the choices that I have in the dropdown menu that I need to assign the %

    image.png


    Rob

    

    · Share on Facebook Share on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi@RobNY2

    If I'm understanding you correctly, you have one specific value that you want to assign as a percentage per choice, and there are two columns where these values could appear. In each row you want to AVG the two values from those two cells for a final percentage.

    If this is the case, I believe you're missing a comma from the first formula. This is the structure:

    =SUM(first value,second value) / 2

    However notice that when you swap from doing an IF statement for the 1st Circuit Status there's no comma. It should be:

    =SUM(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([1st Circuit Status]@row = "Circuit Delivery", 1) + IF([1st Circuit Status]@row = "Circuit in Construction Phase", 0.8), IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([2nd Circuit Status]@row = "Circuit Delivery", 1) + IF([2nd Circuit Status]@row = "Circuit in Construction Phase", 0.8)) / 2


    Since you have a much longer list, what I would suggest doing is two Nested IF statements (assuming that you'll only ever have one value per cell).

    This way you can group together all the same values that have the same output with an IF(OR statement, like so:

    IF(OR([1st Circuit Status]@row= "Contract in Process", [1st Circuit Status]@row= "Waiting for Quote", [1st Circuit Status]@row= "Waiting for Order Validation to complete", [1st Circuit Status]@row= "Cancelled", [1st Circuit Status]@row= "Requirement Review", [1st Circuit Status]@row= "Not Started", [1st Circuit Status]@row= "Out of Scope", [1st Circuit Status]@row= "Reassigned to another Carrier", [1st Circuit Status]@row= "Need to reassign to another Carrier", [1st Circuit Status]@row= "Addendum 37"), 0,


    For a full formula, you could do something like this:

    =SUM(

    IF(OR([1st Circuit Status]@row= "Contract in Process", [1st Circuit Status]@row= "Waiting for Quote", [1st Circuit Status]@row= "Waiting for Order Validation to complete", [1st Circuit Status]@row= "Cancelled", [1st Circuit Status]@row= "Requirement Review", [1st Circuit Status]@row= "Not Started", [1st Circuit Status]@row= "Out of Scope", [1st Circuit Status]@row= "Reassigned to another Carrier", [1st Circuit Status]@row= "Need to reassign to another Carrier", [1st Circuit Status]@row= "Addendum 37"), 0, IF([1st Circuit Status]@row= "Started", .01, IF([1st Circuit Status]@row= "Waiting to assign to local carrier", .05, IF([1st Circuit Status]@row= "Circuit Order in process", .04, IF([1st Circuit Status]@row= "Assigned to Local Carrier", .10, IF([1st Circuit Status]@row= "Circuit in Design Phase", .6, IF([1st Circuit Status]@row= "Circuit Site Survey in Progress", .65, IF([1st Circuit Status]@row= "Circuit Site Survey Complete",.70, IF([1st Circuit Status]@row= "LCON Waiting Carrier to provide Design", .71, IF([1st Circuit Status]@row= "On Hold", .50, IF([1st Circuit Status]@row= "Waiting for LCON to Approve Site Survey", .73, IF([1st Circuit Status]@row= "Circuit in Construction Phase", .8, IF([1st Circuit Status]@row= "Circuit installed waiting carrier to deliver", .99, IF(OR([1st Circuit Status]@row= "Circuit Delivered", [1st Circuit Status]@row= "Use local Current Circuits"), 1),

    IF(OR([2nd Circuit Status]@row= "Contract in Process", [2nd Circuit Status]@row= "Waiting for Quote", [2nd Circuit Status]@row= "Waiting for Order Validation to complete", [2nd Circuit Status]@row= "Cancelled", [2nd Circuit Status]@row= "Requirement Review", [2nd Circuit Status]@row= "Not Started", [2nd Circuit Status]@row= "Out of Scope", [2nd Circuit Status]@row= "Reassigned to another Carrier", [2nd Circuit Status]@row= "Need to reassign to another Carrier", [2nd Circuit Status]@row= "Addendum 37"), 0, IF([2nd Circuit Status]@row= "Started", .01, IF([2nd Circuit Status]@row= "Waiting to assign to local carrier", .05, IF([2nd Circuit Status]@row= "Circuit Order in process", .04, IF([2nd Circuit Status]@row= "Assigned to Local Carrier", .10, IF([2nd Circuit Status]@row= "Circuit in Design Phase", .6, IF([2nd Circuit Status]@row= "Circuit Site Survey in Progress", .65, IF([2nd Circuit Status]@row= "Circuit Site Survey Complete",.70, IF([2nd Circuit Status]@row= "LCON Waiting Carrier to provide Design", .71, IF([2nd Circuit Status]@row= "On Hold", .50, IF([2nd Circuit Status]@row= "Waiting for LCON to Approve Site Survey", .73, IF([2nd Circuit Status]@row= "Circuit in Construction Phase", .8, IF([2nd Circuit Status]@row= "Circuit installed waiting carrier to deliver", .99, IF(OR([2nd Circuit Status]@row= "Circuit Delivered", [2nd Circuit Status]@row= "Use local Current Circuits"), 1)

    /2


    As a final note, make sure that any time you have a quotation mark in a formula it's straight up and down like so: "

    I notice that some of your quotes are curvy (” versus ") which would result in an error in Smartsheet.

    Cheers,

    Genevieve

    · Share on Facebook Share on Twitter
  • RobNY2
    RobNY2 ✭

    Genevieve


    Thank you very much! I appreciate your help!


    Rob

    · 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公式手册模板!

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":25,"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/95012/\")<\/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":39,"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/95012/\")))<\/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:…