Add Formula to entire column, But leave blank

Allen4480
Allen4480 ✭
inFormulas and Functions

Hello,

I've created a "Risk Register".

I created two fields (Severity&Likelihood), which, when filled in equal a "Calculation" column.

However, if I "fill down" the calculation formula, it auto-populates as "high" even though the previous two criteria are blank. If I try "convert to column formula", I get an error.

The Goal:

Only when a new line is created and populated the "calculation" cell is filled.

Here is the formula in row 1 (for example).

=IF(AND(OR(Likelihood1 = "improbable", Likelihood1 = "Possible"), Severity1 = "acceptable"), "Low", IF(AND(Likelihood1 = "Probable", Severity1 = "Acceptable"), "Medium", IF(AND(Likelihood1 = "Probable", Severity1 = "Tolerable"), "High", IF(AND(Likelihood1 = "Improbable", Severity1 = "Undesirable"), "Medium", IF(AND(OR(Likelihood1 = "Probable", Likelihood1 = "Possible", Likelihood1 = "Improbable"), Severity1 = "Tolerable"), "Medium", IF(AND(OR(Likelihood1 = "possible", Likelihood1 = "probable"), Severity1 = "intolerable"), "Extreme", "High"))))))


thank you

Risk__Issue___Decision_Log_-_Smartsheet_com.jpg


· Share on Facebook Share on Twitter

Best Answer

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓

    Hello@Allen4480- There are a few things here. First, you're referencing a specific cell (Severity1) rather than the row (Severity@row), which is why it won't do a column formula. Second, it's populating in other rows because you told it to use "High" if the row doesn't match the other criteria. That means that it's going to say "High" if the cells are blank. Does this work?

    =IF(AND(OR(Likelihood@row = "Improbable", Likelihood@row = "Possible"), Severity@row = "acceptable"), "Low", IF(AND(Likelihood@row = "Probable", Severity@row = "Acceptable"), "Medium", IF(AND(Likelihood@row = "Probable", Severity@row = "Tolerable"), "High", IF(AND(Likelihood@row = "Improbable", Severity@row = "Undesirable"), "Medium", IF(AND(OR(Likelihood@row = "Probable", Likelihood@row = "Possible", Likelihood@row = "Improbable"), Severity@row = "Tolerable"), "Medium", IF(AND(OR(Likelihood@row = "Possible", Likelihood@row = "Probable"), Severity@row = "Intolerable"), "Extreme", IF(Severity@row = "Undesirable", "High")))))))

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

    · Share on Facebook Share on Twitter

Answers

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓

    Hello@Allen4480- There are a few things here. First, you're referencing a specific cell (Severity1) rather than the row (Severity@row), which is why it won't do a column formula. Second, it's populating in other rows because you told it to use "High" if the row doesn't match the other criteria. That means that it's going to say "High" if the cells are blank. Does this work?

    =IF(AND(OR(Likelihood@row = "Improbable", Likelihood@row = "Possible"), Severity@row = "acceptable"), "Low", IF(AND(Likelihood@row = "Probable", Severity@row = "Acceptable"), "Medium", IF(AND(Likelihood@row = "Probable", Severity@row = "Tolerable"), "High", IF(AND(Likelihood@row = "Improbable", Severity@row = "Undesirable"), "Medium", IF(AND(OR(Likelihood@row = "Probable", Likelihood@row = "Possible", Likelihood@row = "Improbable"), Severity@row = "Tolerable"), "Medium", IF(AND(OR(Likelihood@row = "Possible", Likelihood@row = "Probable"), Severity@row = "Intolerable"), "Extreme", IF(Severity@row = "Undesirable", "High")))))))

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

    · Share on Facebook Share on Twitter
  • Allen4480
    Allen4480 ✭

    OMG! you're amazing!!! worked like a charm. thanks, Amber!!!

    · Share on Facebook Share on Twitter
  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭

    My pleasure! If you would mark it as the accepted answer, I would certainly appreciate it. Take care!

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

    · 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公式手册模板!
There we go. The [SO] column looks like it is being manually populated which is storing the data as numerical data, but the LEFT function outputs text.<\/p>


<\/p>

Insert a column on the reference sheet and use<\/p>

=SO@row + \"//www.santa-greenland.com/community/discussion/comment/\"<\/p>

plus quote quote<\/p>


<\/p>

This will convert all entries into a text string so that data types match. You would then match on this helper column in your INDEX\/MATCH.<\/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":112216,"type":"question","name":"Countifs using TODAY()","excerpt":"@Paul Newcome Hello Paul! I'm actually interested in using COUNTIFS for a date that is today or after today. I thought that would be simple lol, but I'm struggling and not super familiar with the @cell functions. In short I want to count open projects based on the team lead. Is there an easier way I'm overlooking or is…","snippet":"@Paul Newcome Hello Paul! I'm actually interested in using COUNTIFS for a date that is today or after today. I thought that would be simple lol, but I'm struggling and not super…","categoryID":322,"dateInserted":"2023-10-26T16:20:58+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T17:28:41+00:00","insertUserID":146147,"insertUser":{"userID":146147,"name":"SabrinaM","url":"https:\/\/community.smartsheet.com\/profile\/SabrinaM","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!nXjWX0mlCpQ!8-NX94Wbwbo!4gxYcN569h-","dateLastActive":"2023-10-27T17:24:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-27T18:29:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":38,"score":null,"hot":3396770379,"url":"https:\/\/community.smartsheet.com\/discussion\/112216\/countifs-using-today","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112216\/countifs-using-today","format":"Rich","lastPost":{"discussionID":112216,"commentID":402080,"name":"Re: Countifs using TODAY()","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402080#Comment_402080","dateInserted":"2023-10-27T17:28:41+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-27T18:29:11+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-10-27T16:59:28+00:00","dateAnswered":"2023-10-27T16:32:35+00:00","acceptedAnswers":[{"commentID":402044,"body":"

In that case you would replace<\/p>

{Cross Sheet Reference}<\/p>


<\/p>

with<\/p>

[Column name]:[Column Name]<\/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":112222,"type":"question","name":"Count Days and Durations","excerpt":"I am looking to count days used in my schedule per task. I am set up with start and finish date columns along with a duration column. I am trying to calculate days on site from start date to today, but to stop counting days when finish date is reached.","snippet":"I am looking to count days used in my schedule per task. I am set up with start and finish date columns along with a duration column. I am trying to calculate days on site from…","categoryID":322,"dateInserted":"2023-10-26T17:03:59+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T15:15:39+00:00","insertUserID":164388,"insertUser":{"userID":164388,"name":"Caleb W","title":"Operations Leader","url":"https:\/\/community.smartsheet.com\/profile\/Caleb%20W","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AFdZucpGLwolOKyzvAL1Qwa-C_qqnmxB9j9mfmL4qM8Hnw=s96-c","dateLastActive":"2023-10-27T17:19:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":113581,"lastUser":{"userID":113581,"name":"Matthew J McAteer","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20J%20McAteer","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zDFUs7dXyHo!7bFXOzNnVPw!fm5dCHPC2D9","dateLastActive":"2023-10-27T18:24:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":41,"score":null,"hot":3396762578,"url":"https:\/\/community.smartsheet.com\/discussion\/112222\/count-days-and-durations","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112222\/count-days-and-durations","format":"Rich","lastPost":{"discussionID":112222,"commentID":402031,"name":"Re: Count Days and Durations","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402031#Comment_402031","dateInserted":"2023-10-27T15:15:39+00:00","insertUserID":113581,"insertUser":{"userID":113581,"name":"Matthew J McAteer","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20J%20McAteer","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zDFUs7dXyHo!7bFXOzNnVPw!fm5dCHPC2D9","dateLastActive":"2023-10-27T18:24:43+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/JDZDOA0P3L7I\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JDZDOA0P3L7I\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JDZDOA0P3L7I\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JDZDOA0P3L7I\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JDZDOA0P3L7I\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JDZDOA0P3L7I\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-27T17:19:33+00:00","dateAnswered":"2023-10-26T22:22:24+00:00","acceptedAnswers":[{"commentID":401941,"body":"

Hi @Caleb W<\/a> <\/p>

Sure, try this: =IFERROR(IF(AND([Start Date]@row <= TODAY(), [End Date]@row > TODAY()), NETWORKDAYS([Start Date]@row, TODAY()), IF(AND([Start Date]@row <= TODAY(), [End Date]@row <= TODAY()), NETWORKDAYS([Start Date]@row, [End Date]@row), \"0\")), \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>

Once you add the formula to a cell, you can right click and convert the formula to a column formula.<\/p>

Hope that helps 😊<\/span><\/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

  • Can I use Index/Match and Left functions in one formula?

    Hello, can someone help me with the left function within the index/match? This is what I tried but it always returns NO MATCH: =INDEX({Master Job SO}, MATCH(LEFT([Job Number]@row,5), {Master Job #}, 0)) I also tried creating a helper column, Job Prefix, using =LEFT([Job Number]@row, 5) and using that result in the SO…

  • Countifs using TODAY()

    @Paul Newcome Hello Paul! I'm actually interested in using COUNTIFS for a date that is today or after today. I thought that would be simple lol, but I'm struggling and not super familiar with the @cell functions. In short I want to count open projects based on the team lead. Is there an easier way I'm overlooking or is…

  • Count Days and Durations

    I am looking to count days used in my schedule per task. I am set up with start and finish date columns along with a duration column. I am trying to calculate days on site from start date to today, but to stop counting days when finish date is reached.