Need help with a formula putting 4 different critera together from 2 columns....

Terri1019

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!

brianschmidt

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!:)

brianschmidt

That's exactly how I would have done it as well. Glad you got it to work!:)

brianschmidt

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!:)

Terri1019

That is exactly what I needed. Thank you so much!!!

Terri1019

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!

Terri1019

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!

brianschmidt

That's exactly how I would have done it as well. Glad you got it to work!:)

Terri1019

Thanks!!