Add Formula to entire column, But leave blank

Allen4480

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

Amber Eakin

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

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

Allen4480

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

Amber Eakin

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