Logic based formula help needed if someone could help please?

Deb Schofield

I am trying to make my sheet derive a priority based upon the responses gathered from a form that is completed.

Users will be selecting from a dropdown list of categories and, based upon their answer I want a priority to populate in another cell. I am not experienced enough to figure out how to build the IF formula (or even know if that is the best way to approach this)

The question and selection of answers is below with the logic responses Low / Medium/ High that I need to generate. There is a slight variation in that the items highlighted yellow in each category need to have an override to a High category because they include Third Party activity.

The column name (as seen below) is Damage Severity and the column name for the formula to live is 'Priority'

Can anyone help?

Thank you

Dale Murphy

@Deb Schofield There are probably more elegant solutions than this one but:

I added a column called Category Major. I use it to deal with the variation, and force category to be only one of three possibilities. That helps keep the IF formula shorter.

The column formula for Category Major is:

=IF(FIND("third party vehicle and property", Category@row) > 0, "Ser", LEFT(Category@row, 3))

** all it does it create a three letter category designation after checking for that vehicle and property condition**

Then in the priority column I built:

=IF(ISBLANK([Category major]@row), "", IF([Category major]@row = "Min", "Low", (IF([Category major]@row = "Mod", "Medium", "High"))))

Seems to work ... just make sure your data entry is clean (e.g., restrict input in category to a set of dropdowns)

dm

Deb Schofield

Thanks Dale - thats really helpful 😀👍️

Dale Murphy

@Deb Schofield There are probably more elegant solutions than this one but:

I added a column called Category Major. I use it to deal with the variation, and force category to be only one of three possibilities. That helps keep the IF formula shorter.

The column formula for Category Major is:

=IF(FIND("third party vehicle and property", Category@row) > 0, "Ser", LEFT(Category@row, 3))

** all it does it create a three letter category designation after checking for that vehicle and property condition**

Then in the priority column I built:

=IF(ISBLANK([Category major]@row), "", IF([Category major]@row = "Min", "Low", (IF([Category major]@row = "Mod", "Medium", "High"))))

Seems to work ... just make sure your data entry is clean (e.g., restrict input in category to a set of dropdowns)

dm

Deb Schofield

Thanks Dale - thats really helpful 😀👍️