Why is my formula not returning a 1 for blank Priority column?

Abby P

I am trying to create a formula for two columns Priority and Director Priority. If Director Priority is populated, I want the cell to return a 3 if High, a 2 if Medium, or a 1 if Low. If Director Priority is blank, I want the cell to return Priority. If the cell is returning Priority, I want the cell to return a 3 if High, a 2 if Medium, or a 1 if Low. If Director Priority AND Priority is blank, I want the cell to return a 1. What should I add to the below formula? I don't think it is picking up the last requirement.

Here is the formula:

=(IF(IF(ISBLANK([Director Priority]@row), Priority@row, [Director Priority]@row) = "High", 3, IF(IF(ISBLANK([Director Priority]@row), Priority@row, [Director Priority]@row) = "Medium", 2, IF(IF(ISBLANK([Director Priority]@row), Priority@row, [Director Priority]@row) = "Low", 1))))

bisaacs

Hey @Abby P,

I think there is a way to make this work for you, we just have to think about the hierarchy of the logic. The easiest thing to check for first is if both Director Priority and Priority cells are blank. If both of those are blank OR Director Priority is listed as Low, then we'll make the cell a 1.

=IF(OR(AND(ISBLANK(Priority@row), ISBLANK([Director Priority]@row)), [Director Priority]@row = "Low"), 1,

If neither of those things are true, then we'll move on to the next check, if Directory priority is "Medium":

IF([Director Priority]@row = "Medium", 2,

Next we'll check if it's high:

IF([Director Priority]@row = "High", 3,

If it's none of those, then the formula will move on and check the Priority cell for the same criteria:

IF(Priority@row = "Low", 1, IF(Priority@row = "Medium", 2, IF(Priority@row = "High", 3))))))

The whole formula will end up looking like this:

=IF(OR(AND(ISBLANK(Priority@row), ISBLANK([Director Priority]@row)), [Director Priority]@row = "Low"), 1, IF([Director Priority]@row = "Medium", 2, IF([Director Priority]@row = "High", 3, IF(Priority@row = "Low", 1, IF(Priority@row = "Medium", 2, IF(Priority@row = "High", 3))))))

Hope this makes sense!