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 (16) 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"), "Fillins")
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"), "Fillins"))))
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"), "Fillins"))))
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"), "Fillin")))))
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
Check out the Formula Handbook template!