Add Formula to entire column, But leave blank

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


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

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

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

  • 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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
@J.Sanabria<\/a> In the notification set up, under Message includes: you would have to select Message only.<\/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":112203,"type":"question","name":"Help w\/ Sumif date is before today","excerpt":"Hi, Hitting a snag w\/ a very basic sumif. I need to sum the total capital investment column if it's corresponding date value falls before or on today's date. Here's the formula I have now: =SUMIF([Fiscal Month End]1:[Fiscal Month End]12, <=TODAY(), [Actual Capital Investment]1:[Actual Capital Investment]12) And a…","snippet":"Hi, Hitting a snag w\/ a very basic sumif. I need to sum the total capital investment column if it's corresponding date value falls before or on today's date. Here's the formula I…","categoryID":322,"dateInserted":"2023-10-26T14:19:00+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T15:04:11+00:00","insertUserID":130047,"insertUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":130047,"lastUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":14,"score":null,"hot":3396663791,"url":"https:\/\/community.smartsheet.com\/discussion\/112203\/help-w-sumif-date-is-before-today","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112203\/help-w-sumif-date-is-before-today","format":"Rich","lastPost":{"discussionID":112203,"commentID":401837,"name":"Re: Help w\/ Sumif date is before today","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401837#Comment_401837","dateInserted":"2023-10-26T15:04:11+00:00","insertUserID":130047,"insertUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+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\/JI13A7YQ5L7E\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T15:04:18+00:00","dateAnswered":"2023-10-26T14:24:37+00:00","acceptedAnswers":[{"commentID":401817,"body":"

Your formula looks good! But is your \"Fiscal Month End\" formatted as a date column? It looks like a text (should have the leading 0's for the months). Double click on the header to change it to a Date type column and you should be good to go!<\/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":112198,"type":"question","name":"How to count one value or combo of values in a column of multiple values","excerpt":"Hi, we have a multi-select dropdown list column, where users chose one or more options. We want to count how many times a user chose an option (or combination of options), or did not choose a specified option. We can count all instances when one particular option was chosen using the following formula, but we cannot figure…","snippet":"Hi, we have a multi-select dropdown list column, where users chose one or more options. We want to count how many times a user chose an option (or combination of options), or did…","categoryID":322,"dateInserted":"2023-10-26T13:14:00+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T14:57:06+00:00","insertUserID":119221,"insertUser":{"userID":119221,"name":"Mike Beam","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Beam","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T14:35:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T16:05:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":33,"score":null,"hot":3396661266,"url":"https:\/\/community.smartsheet.com\/discussion\/112198\/how-to-count-one-value-or-combo-of-values-in-a-column-of-multiple-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112198\/how-to-count-one-value-or-combo-of-values-in-a-column-of-multiple-values","format":"Rich","lastPost":{"discussionID":112198,"commentID":401833,"name":"Re: How to count one value or combo of values in a column of multiple values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401833#Comment_401833","dateInserted":"2023-10-26T14:57:06+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T16:05: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-10-26T14:35:01+00:00","dateAnswered":"2023-10-26T13:50:38+00:00","acceptedAnswers":[{"commentID":401805,"body":"

For Goal 1:<\/p>

=COUNTIFS(Dropdown:Dropdown, COUNTM(@cell) = 1, Dropdown:Dropdown, HAS(@cell, \"Option Name\"))<\/p>

For Goal 2 (with 2 options):<\/p>

=COUNTIFS(Dropdown:Dropdown, COUNTM(@cell) = 2, Dropdown:Dropdown, AND(HAS(@cell, \"Option Name 1\"), HAS(@cell, \"Option Name 2\")))<\/p>

If your options are numbers, you can leave out the quotation marks. <\/p>

If you are wanting to have more than 2 options, increase the COUNTM value and add the extra HAS(@cell, \"Option Name X\") within the AND function.<\/p>

Hope this helps, but I've misunderstood something or have any problems\/questions then just post them up! 🙂<\/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