# Add Formula to entire column, But leave blank

Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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 Formula Handbook template!