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

Options
✭✭✭✭

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

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!

• ✭✭✭✭
Options

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")))))

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

Thanks!!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!