Need help with a formula putting 4 different critera together from 2 columns....
Hello. I have alot to learn about formulas! I have Impact and Effort columns in my sheet. I want to evaluate those numbers (1-6) and give a return in an Impact/Effort Quadrant column. Currently I have this formula which works:
=IF(AND(Impact@row >= "4", Effort@row >= "4"), "Major Project")
The other variables I need to add are:
=IF(AND(Impact@row >= "4", Effort@row <= "3"), "Quick Win")
=IF(AND(Impact@row <= "3", Effort@row >= "4"), "Thankless Task")
=IF(AND(Impact@row <= "3", Effort@row <= "3"), "Fill-ins")
How do I combine all of these for one column formula? Thanks so much for your help!
Best Answers
-
Sounds like you're looking to create a nested if formula. Basically, it goes in the order of your if statements saying, "If this is true, do this. If that's not true move onto the next if statement..."...and so on and so forth. Try this:
=IF(AND(Impact@row >= "4", Effort@row >= "4"), "Major Project", IF(AND(Impact@row >= "4", Effort@row <= "3"), "Quick Win", IF(AND(Impact@row <= "3", Effort@row >= "4"), "Thankless Task", IF(AND(Impact@row <= "3", Effort@row <= "3"), "Fill-ins"))))
Hope this helps!:)
-
That's exactly how I would have done it as well. Glad you got it to work!:)
Answers
-
Sounds like you're looking to create a nested if formula. Basically, it goes in the order of your if statements saying, "If this is true, do this. If that's not true move onto the next if statement..."...and so on and so forth. Try this:
=IF(AND(Impact@row >= "4", Effort@row >= "4"), "Major Project", IF(AND(Impact@row >= "4", Effort@row <= "3"), "Quick Win", IF(AND(Impact@row <= "3", Effort@row >= "4"), "Thankless Task", IF(AND(Impact@row <= "3", Effort@row <= "3"), "Fill-ins"))))
Hope this helps!:)
-
That is exactly what I needed. Thank you so much!!!
-
Hi Brian,
I do have one more issue I discovered. Because I am using a < symbol, if either the Impact or Effort cell is blank with a number not assigned, it will fall into the <3 quadrant. Is there a way to have the "Impact/Effort Quadrant" column remain blank is there is not a number assigned in either the Impact or the Effort column?
Does that make sense? Thanks so much!
-
Brian,
I believe I got it to work:
=IF(OR(Impact@row = "", Effort@row = ""), "", IF(AND(Impact@row >= "4", Effort@row >= "4"), "Major Project", IF(AND(Impact@row >= "4", Effort@row <= "3"), "Quick Win", IF(AND(Impact@row <= "3", Effort@row >= "4"), "Thankless Task", IF(AND(Impact@row <= "3", Effort@row <= "3"), "Fill-in")))))
Thanks for your help!
-
That's exactly how I would have done it as well. Glad you got it to work!:)
-
Thanks!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!