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

  • brianschmidt
    brianschmidt ✭✭✭✭✭
    Answer ✓

    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
    brianschmidt ✭✭✭✭✭
    Answer ✓

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

Answers

  • brianschmidt
    brianschmidt ✭✭✭✭✭
    Answer ✓

    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
    Terri1019 ✭✭✭

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

  • Terri1019
    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
    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
    brianschmidt ✭✭✭✭✭
    Answer ✓

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!