Why is my formula not returning a 1 for blank Priority column?
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))))
Answers
-
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!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!